MySQL DISTINCT & GROUP BY

[Anon]

I have a question regarding MySQL DISTINCT and GROUP BY.

SELECT * FROM test

id first_name last_name date
-- ---------- --------- ----
1 Tadej Lasic 2001-01-01
2 Luka Jerebic 2001-01-02
3 Pajo Makina 2001-01-03
4 Tadej Lasic 2001-01-04
5 Pajo Makina 2001-01-05

As you can see I have some identical first and last names. I would like to only display
them once and sort them by date. So it should display something like this:

id first_name last_name date
-- ---------- --------- ----
5 Pajo Makina 2001-01-05
4 Tadej Lasic 2001-01-04
2 Luka Jerebic 2001-01-02

Now, if I use the GROUP BY clause, which groups fields from the beginning to the end, I get
this:

SELECT * FROM test GROUP BY first_name ORDER BY date DESC

id first_name last_name date
-- ---------- --------- ----
3 Pajo Makina 2001-01-03
2 Luka Jerebic 2001-01-02
1 Tadej Lasic 2001-01-01

But that's not what I want. I would like to display distinct names, which where created the last
not the first.

I've tried it with DISTINCT but it only works/displays fields which have identical values (fist_name, last_name).

Can this be done and how? Any help would be appreciated.

[Anon]

Best I can do for you quickly is:-

select first_name,
last_name,
max(date)
from test
group by first_name,
last_name

If you need the id as well then this won't do although to be fair I have only run this under Oracle as I don't have access to MySQL today so you could try it as MySQL does have some difference to Oracle in the group by/ order by area.

This would be much easier if MySQL supported subselects (i.e. selects within the where clauses of other selects), but there you go.

Hope this is of some help,

Chris.

[Anon]

Thank you very much! It works! You can also get the id:

select max(id) as id, first_name, last_name, max(date) as date1 from test group by first_name, last_name order by date1 desc

id first_name last_name date1
-- ---------- --------- -----
5 Pajo Makina 2001-01-05
4 Tadej Lasic 2001-01-04
2 Luka Jerebic 2001-01-02

YES! 🙂

[Anon]

Be careful here as I think it may only work as the max id's happen to match the max dates. If the id is auto incremented and the records are input in date order then this will be Ok, but if that is not the case then ... well suffice to say it may not work.

Glad to have been of some assistance.

Best Regards,

Chris.