Use of GROUP BY twice in MySQL

mysql, sql

Solution

This query returns the exact final results you're looking for (example):

SELECT `final`.*
FROM `tableName` AS `final`
JOIN (
    SELECT `thead`.`Id`, `Head`, MIN(`intTime`) AS `min_intTime`
    FROM `tableName` AS `thead`
    JOIN (
        SELECT `Id`, MIN(intTime) as min_intTime
        FROM `tableName` AS `tid`
        GROUP BY `Id`
    ) `tid`
    ON `tid`.`Id` = `thead`.`Id`
    AND `tid`.`min_intTime` = `thead`.`intTime`
    GROUP BY `Head`
) `thead`
ON `thead`.`Head` = `final`.`Head`
AND `thead`.`min_intTime` = `final`.`intTime`
AND `thead`.`Id` = `final`.`Id`

How it works

The innermost query groups by `Id`, and returns the `Id` and corresponding `MIN(intTime)`. Then, the middle query groups by `Head`, and returns the `Head` and corresponding `Id` and `MIN(intTime)`. The final query returns all rows, after being narrowed down. You can think of the final (outermost) query as a query on a table with only the rows you want, so you can do additional comparisons (e.g. `WHERE final.intTime > 3`).

Problem

My table looks like this. ``` Location Head Id IntTime 1 AMD 1 1 2 INTC 3 3 3 AMD 2 2 4 INTC 4 4 5 AMD2 1 0 6 ARMH 5 1 7 ARMH 5 0 8 ARMH 6 1 9 AAPL 7 0 10 AAPL 7 1 ``` Location is the primary key. I need to GROUP BY Head and by Id and when I use GROUP BY, I need to keep the row with the smallest IntTime. After the first GROUP BY Id, I should get (I keep the smallest IntTime) ``` Location Head Id IntTime 2 INTC 3 3 3 AMD 2 2 4 INTC 4 4 5 AMD2 1 0 7 ARMH 5 0 8 ARMH 6 1 9 AAPL 7 0 ``` After the second GROUP BY Head, I should get (I keep the smallest IntTime) ``` Location Head Id IntTime 2 INTC 3 3 3 AMD 2 2 5 AMD2 1 0 7 ARMH 5 0 9 AAPL 7 0 ``` When I run the following command, I keep the smallest IntTime but the rows are not conserved. ``` SELECT Location, Head, Id, MIN(IntTime) FROM test GROUP BY Id ``` Also, to run the second GROUP BY, I save this table and do again ``` SELECT Location, Head, Id, MIN(IntTime) FROM test2 GROUP BY Head ``` Is there a way to combine both commands? [Edit: clarification] The result should not contain two Head with the same value or two Id with the same value. When deleting those duplicates, the row with the smallest IntTime should be kept.

Original source