how to select only row with max sequence without using a subquery?

Question

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

This appears to work

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?

Solution 1

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 returns the sequential number of a row within a partition of a result set.

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

Solution 2

In general, you neeed to use windowing or ranking functions - Rank(), Row_number(), etc.

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

This will work in SQL Server 2005+ - in oracle you may need to specify the field names explicitly, instead of the *

Solution 3

Just in case you use an RDBMS that doesn't support window functions, you can use:

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

It still involves a subquery, but I don't see a way of doing this without one, nor do I really understand why you would want to avoid them.

Comments

[OzrenTkalcecKrznaric]

This is the best way I could have thought of as well. However, there are two select clauses in it, and if you don't like that, you could just take inner select and in your app saw off everything that's not ranked 1 ;)

[superachu]

thanks for refreshing my brain, I used this in past and using it now after a long time, it works perfectly!