user1735921 user1735921 - 5 months ago 12
SQL Question

Mysql - Order in group by , select latest from group

See I have a table

id col1 col2
1 a x
2 b y
3 c z
4 a x
5 b y
6 c z
7 a x
8 b y
9 c z
10 a p
11 b q
12 c r


To get that you need to do these queries:

create database test_db_one;
use test_db_one;
create table test_table_one (
id INT NOT NULL AUTO_INCREMENT,
col1 varchar(3),
col2 varchar(4),
PRIMARY KEY (id)
);
INSERT INTO test_table_one(col1,col2) VALUES ('a','x'),('b','y'),('c','z'),('a','x'),('b','y'),('c','z'),('a','x'),('b','y'),('c','z'),('a','p'),('b','q'),('c','r');


I want following result,

id col1 col2
7 a x
8 b y
9 c z
10 a p
11 b q
12 c r


I do group by col1,col2 to get the above result

select * from test_table_one group by col1,col2;


But I get wrong result which is

+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 10 | a | p |
| 1 | a | x |
| 11 | b | q |
| 2 | b | y |
| 12 | c | r |
| 3 | c | z |
+----+------+------+


I don't want id 3,2,1 here, I want ids 9,8,7 instead of them, they should be latest, I hope you got what I meant, the latest ids from the group.
Been struggling with it now, any ideas?

Edit:
I can't do ORDER BY (order by does not give the required result anyways).

Answer

Well, this can be simply solved with MAX() :

SELECT MAX(t.id) as max_id,t.col1,t.col2
FROM test_table_one
GROUP BY t.col1,t.col2
ORDER BY max_id
Comments