SQL get rows based on two ids

Question

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)

Look here : http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column And i think this is pointing you in the right direction: SELECT outer.* FROM tableName AS outer LEFT OUTER JOIN tableName as inner ON outer.id = inner.id AND outer.client_id < inner.client_id WHERE inner.id IS NULL; — Nyranith, Jul 21 '16 at 18:49

score 3 · Accepted Answer · answered Jul 21 '16 at 18:44

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;

Man thats amazing ;) i never had figured this out on my own – GDY Jul 21 '16 at 19:00 — GDY, Jul 21 '16 at 19:00

score 2 · Answer 2 · answered Jul 21 '16 at 18:47

2

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

this should be more efficient than a sub select

answered Jul 21 '16 at 18:47

bill

1,646
1
18
27

Looks great ... nice and clean. But i get the following error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xyz.properties.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – GDY Jul 21 '16 at 19:02
Ok is just run set sql_mode = '' directly after the db connect like mentioned here and it works now: http://stackoverflow.com/a/23921234/2593099 – GDY Jul 21 '16 at 19:06
Hmm looks like i still doesn't work correctly. It returns wrong fields. – GDY Jul 21 '16 at 19:11
What is it returning? As scarisEdge pointed out, you could specify the fields in the select. So `SELECT table.id, table.client_id FROM table GROUP BY client_id HAVING max(table.id)` – bill Jul 21 '16 at 19:15
Looks like the HAVING is applied after the grouping. I got table rows like this 1|1, 2|1 and 3|2 but as return i get 1 and 3 – GDY Jul 21 '16 at 19:16
Ah, the last block in @scarisEdge's answer is probably the way to go then. – bill Jul 21 '16 at 19:18
The first it is because i want to select *. Nevertheless thank you for your time ;) – GDY Jul 21 '16 at 19:19

SQL get rows based on two ids

2 Answers2