How to Select the Maximum Value within Each Group in SQL?

SQL Group By with Maximum Value Selection

In SQL, the GROUP BY clause allows us to group rows based on specified columns. When used with the MAX function, it can be used to select the largest value from each group.

Problem Statement

Suppose you have a table containing the following data:

ID	Name	AlarmID	AlarmUnit	AlarmLevel
1	Test	Voltage	PSU	Warning
2	Test	Voltage	PSU	Ceased
3	Test	Voltage	PSU	Warning
4	Test	Temperature	RCC	Warning
5	Test	Temperature	RCC	Ceased

You want to retrieve only the most recent information for each group defined by AlarmID and AlarmUnit. The expected output should be:

ID	Name	AlarmID	AlarmUnit	AlarmLevel
3	Test	Voltage	PSU	Warning
5	Test	Temperature	RCC	Ceased

Solution

To solve this problem, we can use the following query in Oracle, SQL Server 2005 , and PostgreSQL 8.4 :

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY alarmId, alarmUnit ORDER BY id DESC) AS rn
        FROM    TableName
        ) q
WHERE   rn = 1

For MySQL, the query is:

SELECT  mi.*
FROM    (
        SELECT  alarmId, alarmUnit, MAX(id) AS mid
        FROM    TableName
        GROUP BY
                alarmId, alarmUnit
        ) mo
JOIN    TableName mi
ON      mi.id = mo.mid

For PostgreSQL 8.3 and below, we can use:

SELECT  DISTINCT ON (alarmId, alarmUnit) *
FROM    TableName
ORDER BY
        alarmId, alarmUnit, id DESC

The above is the detailed content of How to Select the Maximum Value within Each Group in SQL?. For more information, please follow other related articles on the PHP Chinese website!