如何只选择具有最大序列的行而不使用子查询? [英] how to select only row with max sequence without using a subquery?

问题描述

我试图为每个ID只选择seq最高的行

I'm trying to select only the row with the highest seq for each ID

ID  |  Seq   |  Age
-------------------
 A      1       20   
 A      2       30
 B      1       25
 B      2       32
 B      3       44
 B      4       48
 C      1       11

这似乎起作用

SELECT ID, Age
FROM Persons a
WHERE Seq = (SELECT MAX(Seq) FROM Persons b WHERE a.ID = b.ID)

但这是最好的方法，唯一的方法吗?如果不需要，我不喜欢使用子查询，我记得您可以使用某些东西，但我忘记了它是什么.有什么主意吗?

But is this the best way, the only way? I don't like using subqueries if I don't have to and I recall you can use something but I forget what it is. Any idea?

推荐答案

假定SQL Server(> = 2005)或Oracle(10g?):

Assuming SQL-Server ( >= 2005) or Oracle (10g?):

WITH CTE AS
( 
   SELECT
       ROW_NUMBER() OVER (PARTITION BY ID  ORDER BY Seq DESC) AS RN
       , ID, Age
   FROM 
       Persons
)
SELECT ID, Age 
FROM CTE
WHERE RN = 1

ROW_NUMBER 返回a内行的顺序号结果集的分区.

ROW_NUMBER returns the sequential number of a row within a partition of a result set.

编辑:您也可以在Oracle中看到它: http://sqlfiddle.com/#!4/b7e79/2/0

Edit: works also in Oracle as you can see here: http://sqlfiddle.com/#!4/b7e79/2/0

这篇关于如何只选择具有最大序列的行而不使用子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！