SQL Group by&马克斯 [英] SQL Group by & Max

问题描述

我在表格中有以下数据：

  id名称alarmId alarmUnit alarmLevel 
 
 1测试电压psu警告
 2测试电压停止
 3测试电压psu警告
 4测试温度rcc警告
 5测试温度rcc停止
  

我想仅显示有关每个colums组（alarmId，alarmUnit）的最新信息，因此结果应如下所示：

  3测试电压psu警告
 5测试温度rcc停止
  

我已经尝试过了：

SELECT MAX（id）as id ，name，alarmId，alarmUnit，alarmLevel GROUP BY alarmId，alarmUnit;

所选ID似乎没问题，但所选行不对应于他们。您可以帮助我吗？

解决方案在 Oracle 中， SQL Server 2005 + 和 PostgreSQL 8.4 ：

  SELECT * 
 FROM（
 SELECT *，ROW_NUMBER（）OVER（PARTITION BY alarmId，alarmUnit ORDER BY id DESC）AS 
 FROM mytable 
）q 
 
  

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 
  




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?

解决方案

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

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