brewphone brewphone - 19 days ago 9
SQL Question

MySQL, to reverse SELECT GROUP BY

I have checked the posts with the similar question (1, 2, 3 ...) but no solution works for me on MySQL57 (mysql-installer-community-5.7.13.0.msi). What I want to do is do "group by" in reverse direction (from bottom of the table going up). Really don't understand why following query doesn't work.

use db;

# create the table. k guarantees increase.
CREATE TABLE `db`.`test` (
`k` INT NOT NULL,
`a` INT NULL,
`b` INT NULL,
`c` INT NULL,
PRIMARY KEY (`k`));

# populate data
INSERT INTO `db`.`test` (`k`, `a`, `b`, `c`) VALUES ('1', '1', '10', '100');
INSERT INTO `db`.`test` (`k`, `a`, `b`, `c`) VALUES ('2', '2', '20', '200');
INSERT INTO `db`.`test` (`k`, `a`, `b`, `c`) VALUES ('3', '1', '10', '300');
INSERT INTO `db`.`test` (`k`, `a`, `b`, `c`) VALUES ('4', '3', '30', '700');
INSERT INTO `db`.`test` (`k`, `a`, `b`, `c`) VALUES ('5', '3', '30', '800');

# want to query the last entry of each unique a+b
Select * From
(Select * From test Order By k Desc) As t
Group By t.a, t.b;


what I get is

k a b c
1 1 10 100
2 2 20 200
4 3 30 700


but what I want is as following, order does not matter.

k a b c
5 3 30 800
3 1 10 300
2 2 20 200

Answer

You don't want group by at all. You just want intelligent filtering:

Select t.*
From test t
where t.k = (select max(t2.k)
             from test t2
             where t2.a = t.a and t2.b = t.b
            );