Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Use of GROUP BY twice in MySQL

Tags:

sql

mysql

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.

like image 300
Youcha Avatar asked Jun 21 '12 19:06

Youcha


People also ask

Can we use GROUP BY 2 times in SQL?

Yes, it is possible to use MySQL GROUP BY clause with multiple columns just as we can use MySQL DISTINCT clause. Consider the following example in which we have used DISTINCT clause in first query and GROUP BY clause in the second query, on 'fname' and 'Lname' columns of the table named 'testing'.

What does GROUP BY 2 mean in SQL?

Consider above queries: Group by 1 means to group by the first column and group by 1,2 means to group by the first and second column and group by 1,2,3 means to group by first second and third column.

Does GROUP BY Make query slow?

In this article, you will learn how SQL aggregate functions can represent an easy way to significantly improve your… The main problem with GROUP BY is that queries involving it are usually slow, especially when compared with WHERE -only queries.

Can we GROUP BY 2 columns in SQL?

SELECT Statement: The GROUP BY Clause in SQLA GROUP BY clause can contain two or more columns—or, in other words, a grouping can consist of two or more columns.


1 Answers

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).

like image 130
0b10011 Avatar answered Sep 18 '22 13:09

0b10011