SQL集团通过和马克斯(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

我想只显示大约每colums组（alarmId，alarmUnit）的最新信息，所以结果应该是这样的：

3   test    voltage  psu        warning
5   test    temp     rcc        ceasing

到目前为止，我已经试过：

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

选择标识似乎是罚款，但选择的行不对应于它们。 你可以帮帮我吗？

Answer 1:

在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

Answer 2:

如果你想获得最大的行，你可能需要一个子查询。 就像是：

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

Answer 3:

尝试：

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

Answer 4:

也许尝试类似如下：

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

您可能需要包括alarmId和alarmUnit在子查询中选择。

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

文章来源: SQL Group by & Max