как выбрать только строку с максимальной последовательностью без использования подзапроса?

Я пытаюсь выбрать только строку с наивысшей последовательностью для каждого идентификатора.

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)

Но лучший ли это путь, единственный ли он? Я не люблю использовать подзапросы, если в этом нет необходимости, и я помню, что вы можете что-то использовать, но я забываю, что это такое. Есть идеи?


sql sql-server oracle common-table-expression
person jenswirf    schedule 31.10.2012    source источник
comment
В основном SQL Server, но если он совместим с оракулом, то это круто   -  person jenswirf    schedule 31.10.2012
comment
Мой подход ROW_NUMBER работает и в Oracle, как я тестировал с помощью SQl-Fiddle. Посмотрите ссылку в моем ответе.   -  person Tim Schmelter    schedule 31.10.2012

Ответы (3)


arrow_upward
6
arrow_downward

Предполагая SQL-сервер ( >= 2005) или 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 возвращает порядковый номер строки в разделе результирующего набора. .

Изменить: работает также в Oracle, как вы можете видеть здесь: http://sqlfiddle.com/#!4/b7e79/2/0

person Tim Schmelter    schedule 31.10.2012
comment
спасибо за освежение моего мозга, я использовал это в прошлом и использую его сейчас спустя долгое время, он работает отлично! - person superachu; 13.12.2016

arrow_upward
1
arrow_downward

Как правило, вам нужно использовать оконные функции или функции ранжирования — Rank(), Row_number() и т. д.

select *
from
(
    select *, row_number() over (partition by id order by age desc) rn
    from yourtable
) v
where rn = 1

Это будет работать в SQL Server 2005+ - в oracle вам может потребоваться явно указать имена полей вместо *

person podiluska    schedule 31.10.2012
comment
Это лучший способ, о котором я мог подумать. Однако в нем есть два предложения select, и если вам это не нравится, вы можете просто взять внутренний выбор и в своем приложении отпилить все, что не занимает 1 место;) - person OzrenTkalcecKrznaric; 31.10.2012

arrow_upward
1
arrow_downward

На всякий случай, если вы используете СУБД, которая не поддерживает оконные функции, вы можете использовать:

SELECT  Persons.ID, Persons.Age, Persons.Seq
FROM    Persons
        INNER JOIN
        (   SELECT  Persons.ID, MAX(Seq) AS Seq
            FROM    Persons
            GROUP BY Persons.ID
        ) MaxP
            ON MaxP.ID = Persons.ID
            AND MaxP.Seq = Persons.Seq

Это все еще включает в себя подзапрос, но я не вижу способа сделать это без него, и я действительно не понимаю, почему вы хотели бы избежать их.

person GarethD    schedule 31.10.2012