SQL Group by &最大限度 [英] SQL Group by & Max

问题描述

我在表中有以下数据:

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

我只想显示有关每个列组的最新信息 (alarmId,alarmUnit)，因此结果应如下所示:

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;

选定的 ID 似乎没问题，但选定的行与它们不对应.你能帮我吗?

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

推荐答案

在 Oracle、SQL Server 2005+ 和 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

在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

在 PostgreSQL 8.3 及以下:

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

这篇关于SQL Group by &最大限度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！