Grandy Grandy - 4 months ago 7
MySQL Question

SQL get rows based on two ids

I really don't know how to title this problem properly.

Heres the table structure:

ID | CLIENT_ID | …


ID
is primary and auto increment.
CLIENT_ID
on the other hand can occur multiple times.

What i want is to fetch the rows by
CLIENT_ID
with highest
ID
... Heres a example

ID | CLIENT_ID
1 | 1
2 | 1
3 | 2
4 | 3
5 | 2


So here
CLIENT_ID
1 and 2 occurs multiple times (because there is a newer version).

After the query i want the following
ID
s in the results: 2,4,5 (Because the highest
ID
in rows with
CLIENT_ID
1 is the row with
ID
2 and so on)

Answer

If you need all the columns you can use a select in

 select * from my_table 
 where (id, client_id) in ( select max(id), client_id 
                            from my_table 
                            group by client_id);

but if you need only the id

 select id  from my_table 
 where (id, client_id) in ( select max(id), client_id 
                            from my_table 
                            group by client_id);

or more simple

   select max(id) 
   from my_table 
   group by client_id;