mysql SQL status GROUP BY DISTINCT

Question

I have a status Database like the following:

id    foreign_id    status
1     1             1
2     5             2
3     2             0
4     5             4

This Database relates some objects (foreign_id) with some status. My Aim is now to get for all the foreign_ids the last status. Unfortunately if I use DISTINCT on foreign_id my mysql-Database choose by random a status, not the latest. Even if I order by id DESC it doesn't work. How can I manage to get my Information?

In this example my desired output would be:

id    foreign_id    status
1     1             1
3     2             0
4     5             4

Note: The second line is missing, because there is a newer line (4) for the foreign_id "5".

I already tried a GROUP BY construction, but it fails the same way, DISTINCT does.

sql server is a different product, pls always use the appropriate product tags only! — Shadow, Apr 07 '16 at 09:00

score 0 · Accepted Answer · answered Apr 07 '16 at 08:59

0

Do a self join:

select t1.*
from tablename t1
join (select foreign_id, max(id) as id from tablename group by foreign_id) t2
  on t1.foreign_id = t2.foreign_id and t1.id = t2.id

The sub-query is there to find each foreign_id's highest id value. Use those values to return the whole rows.

answered Apr 07 '16 at 08:59

jarlh

42,561
8
45
63

Thank you, this is exactly, what I need! You saved my ass! – Michèle S. Apr 07 '16 at 09:35

mysql SQL status GROUP BY DISTINCT

1 Answers1