user3348353 user3348353 - 4 months ago 7
SQL Question

MySQL group by choosing the most complete record

I have a list of records like this:

id state description date
3340 KO test 2016-07-27
3340 KO 2016-07-27
3340 AQ blalba 2016-07-27
3340 AC 2016-07-27


I need to group by state choosing the most complete record between duplicated states records like the result below:

id state description date
3340 KO test 2016-07-27
3340 AQ blalba 2016-07-27
3340 AC 2016-07-27

Answer

This work around can help you to get your expected result:

SET @row_number = 0;

SELECT id, `state`, `description`, `date`
FROM (
    SELECT *, (@row_number:=@row_number + 1) AS num
    FROM TestTable
    ORDER BY IF (`description` <> '', 0, 1)
    ) T
GROUP BY id, `state`

Working DEMO

Sample execution with the given data:

CREATE TABLE TestTable(id INT, `state` VARCHAR(2), `description` VARCHAR(100), `date` DATE);

INSERT INTO TestTable (id, `state`, `description`, `date`) VALUES
(3340, 'KO', ''       , '2016-07-27'),
(3340, 'KO', 'test'   , '2016-07-27'),
(3340, 'AQ', 'blalba' , '2016-07-27'),
(3340, 'AC', ''       , '2016-07-27');

SET @row_number = 0;

SELECT id, `state`, `description`, `date`
FROM (
    SELECT *, (@row_number:=@row_number + 1) AS num
    FROM TestTable
    ORDER BY IF (`description` <> '', 0, 1)
    ) T
GROUP BY id, `state`
Comments