sunny rajpal sunny rajpal - 5 months ago 11
SQL Question

find other columns value based on maximum of one column using groupby particular column

I have data like below

+-------+---------+--------+
| Count | Mindif | Device |
+-------+---------+--------+
| 45 | 3 | A |
| 78 | 4 | A |
| 52 | 5 | A |
| 24 | 6 | A |
| 22 | 1 | B |
| 22 | 2 | B |
| 34 | 3 | B |
| 37 | 4 | B |
| 52 | 5 | B |
| 34 | 6 | B |
| 13 | 1 | C |
| 30 | 2 | C |
| 57 | 3 | C |
| 111 | 4 | C |
| 35 | 5 | C |
+-------+---------+--------+


Want to find Mindif and device based on max value of count.
Output be like

+-------+---------+--------+
| Count | Mindif | Device |
+-------+---------+--------+
| 78 | 4 | A |
| 52 | 5 | B |
| 111 | 4 | C |
+-------+---------+--------+

Answer

You can use a query like this:

SELECT t1.Count, t1.Mindif, t1.Device
FROM mytable AS t1
JOIN (
   SELECT Device, MAX(Count) AS Count
   FROM mytable 
   GROUP BY Device
) AS t2 ON t1.Device = t2.Device AND t1.Count = t2.Count

The query uses a derived table that returns the max Count value per Device. Joining back to the original table we can get the desired result.