SQL Group by & Max

Question

Hello,

I have a following data in a table:

id  name alarmId alarmUnit alarmLevel

1   test voltage psu warning

2   test voltage psu ceasing

3   test voltage psu warning

4   test temp rcc warning

5   test temp rcc ceasing

I'd like to show only the most recent information about every colums group (alarmId,alarmUnit), so the result should look like this:

3   test voltage psu warning

5   test temp rcc ceasing

I've tried so far:

SELECT MAX(id) as id,name,alarmId,alarmUnit,alarmLevel GROUP BY alarmId,alarmUnit;

Selected IDs seem to be fine but selected rows aren't corresponding to them. Could you help me?

Answer 1

If you want to get the row of the max, you'll probably need a sub-query. Something like:

SELECT *
FROM YourTable
WHERE id IN (
    SELECT MAX(id) FROM YourTable GROUP BY alarmId, alarmUnit
    )

Answer 2

Try:

SELECT * FROM table WHERE id IN
  (SELECT MAX(id) FROM table GROUP BY alarmId, alarmUnit)

Answer 3

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    mytable
        ) q
WHERE   rn = 1

In MySQL:

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

In PostgreSQL 8.3 and below:

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

Answer 4

Maybe try something like the following:

SELECT id,name,alarmId,alarmUnit,alarmLevel
FROM table
WHERE id IN (SELECT Max(id) FROM table GROUP BY alarmId, alarmUnit)

You may have to include alarmId and alarmUnit in the sub query select.

Answer 5

select id, name, alarmID, alarmUnit, alarmLevel
from (select max(id) as id
from table
group by alarmID, alarmUnit) maxID
inner join table
on table.id = maxID.id