SQL Group by & Max
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1299556/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Group by & Max
提问by Quassnoi
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:
我只想显示有关每个列组的最新信息 (alarmId,alarmUnit)，因此结果应如下所示：
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;
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?
选定的 ID 似乎没问题，但选定的行与它们不对应。你可以帮帮我吗？
回答by Quassnoi
In Oracle, SQL Server 2005+and PostgreSQL 8.4:
在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
In MySQL:
在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.3and below:
在PostgreSQL 8.3及以下：
SELECT DISTINCT ON (alarmId, alarmUnit) *
FROM mytable
ORDER BY
alarmId, alarmUnit, id DESC
回答by Chris Shaffer
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
)
回答by bernhof
Try:
尝试：
SELECT * FROM table WHERE id IN
(SELECT MAX(id) FROM table GROUP BY alarmId, alarmUnit)
回答by mattruma
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.
您可能必须在子查询选择中包含 alarmId 和 alarmUnit。
回答by user158017
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
