Lewis Day Lewis Day - 5 months ago 17
MySQL Question

Group and Order By

I'm unsure if I'm using the

GROUP BY
function correctly, What I'm trying to achieve is for this query to select the latest input from the table and group them by the
username
, Below I have attached my query, the example database and the output I would like to achieve.

SQL

SELECT * FROM `test_pms`
WHERE `username` = 'Lewis'
GROUP BY `username`
ORDER BY `id` DESC


Database Structure

enter image description here

Current Output

Lewis | hi

Desired Output

Lewis | hi :)

Hopefully I have outlined the issue I'm having with the
GROUP BY
I'm wondering If I'm using the
ORDER BY
incorrectly in accordance with the
GROUP BY
, Thankyou for any help.

Answer

If you specified a username like 'lewis', then you can do it like this:

select *
from `test_pms`
where `username` = 'lewis'
order by `id` desc
limit 1

And if you want get latest record by each username group, you can do it like this:

SELECT `test_pms`.*
FROM `test_pms`
INNER JOIN (SELECT MAX(id) AS id, username FROM `test_pms` GROUP BY username) t
ON t.id = `test_pms`.id AND t.username = `test_pms`.username
GROUP BY `test_pms`.`username`

DEMO HERE

Comments