CODEWITHSUNDEEP
mysqlselect
GDY
GDY

Reputation: 2941

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 IDs 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)

Upvotes: 2

Views: 83

Answers (2)

bill
bill

Reputation: 1656

SELECT * FROM table GROUP BY client_id HAVING max(id)

this should be more efficient than a sub select

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

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;

Upvotes: 3

Related Questions