Uli Uli - 1 month ago 10
SQL Question

Find minimum value from comparing specific columns, but show all columns and rows - MySQL

I want to find the minimum value from comparing specific columns, but show all columns and rows. I hope this example makes it clearer.

This is my data:

+----+----+-----+-----+
| PK | FK | A | B |
+----+----+-----+-----+
| 1 | 1 | abc | tzu |
| 2 | 1 | def | qwe |
| 3 | 2 | abc | tzu |
| 4 | 2 | def | qwe |
| 5 | 3 | def | qwe |
+----+----+-----+-----+


Now, I am looking for the minimum value of PK (Min_PK) by comparing columns A and B (duplicates) for each row. The result should look like this:

+--------+----+----+-----+-----+
| Min_PK | PK | FK | A | B |
+--------+----+----+-----+-----+
| 1 | 1 | 1 | abc | tzu |
| 2 | 2 | 1 | def | qwe |
| 1 | 3 | 2 | abc | tzu |
| 2 | 4 | 2 | def | qwe |
| 2 | 5 | 3 | def | qwe |
+--------+----+----+-----+-----+


I tried several thing:

SELECT MIN(PK) AS Min_PK, `table`.`A`, `table`.`B`
FROM `test`.`table`
GROUP BY `table`.`A`, `table`.`B`;
+--------+-----+-----+
| Min_PK | A | B |
+--------+-----+-----+
| 1 | abc | tzu |
| 2 | def | qwe |
+--------+-----+-----+


This just returns the duplicates, but not all columns and rows. If I add PK and FK to GROUP BY, it will compare all columns for duplicates.

This is getting closer:

SELECT
MIN(PK) AS Min_PK,
GROUP_CONCAT(`table`.`PK`),
GROUP_CONCAT(`table`.`FK`),
`table`.`A`,
`table`.`B`
FROM `test`.`table`
GROUP BY
`table`.`A`,
`table`.`B`;
+--------+----------------------------+----------------------------+-----+-----+
| Min_PK | GROUP_CONCAT(`table`.`PK`) | GROUP_CONCAT(`table`.`FK`) | A | B |
+--------+----------------------------+----------------------------+-----+-----+
| 1 | 1,3 | 1,2 | abc | tzu |
| 2 | 2,4,5 | 1,2,3 | def | qwe |
+--------+----------------------------+----------------------------+-----+-----+


Here I have all the information I want, but it is not the right form. I want it separated by row.
Could you help me with this issue?

Answer

You can use your first query as a derived table and join back to the original table so as to get the rest of the fields:

SELECT t2.Min_PK, t1.*
FROM `test`.`table` AS t1
JOIN (
   SELECT MIN(PK) AS Min_PK, `table`.`A`, `table`.`B`
   FROM `test`.`table`
   GROUP BY `table`.`A`, `table`.`B`
) AS t2 ON t1.A = t2.A AND t1.B = t2.B
Comments