CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Yatiac
Yatiac

Reputation: 2010

Select Max two rows of each account SQL Server

I have this table

ID     AGE     ACCNUM     NAME
--------------------------------
1      10      55409      Intro
2      6       55409      Chapter1
3      4       55409      Chapter2
4      3       69591      Intro
5      6       69591      Outro
6      0       40322      Intro

And I need a query that returns the two max age from each ACCNUM in this case, records: 

1, 2, 4, 5, 6

I have tried too many queries but nothing works for me.

I tried this query

Select 
    T1.accnum, T1.age 
from 
    table1 as T1
inner join 
    (select 
         accnum, max(age) as max 
     from table1 
     group by accnum) as T2 on T1.accnum = T2.accnum 
                            and (T1.age = T2.max or T1.age = T2.max -1)

Upvotes: 0

Views: 2753

Answers (3)

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

CODE:

WITH CTE AS (SELECT ID, AGE, ACCNUM, NAME,
             ROW_NUMBER() OVER(PARTITION BY ACCNUM ORDER BY AGE DESC) AS ROW_NUM
             FROM T1)
SELECT ID, AGE, ACCNUM, NAME
FROM CTE
WHERE ROW_NUM <= 2

Uses a common table expression to achieve the desired result.

SQL Fiddle

Upvotes: 1

Milen
Milen

Reputation: 8877

TSQL Ranking Functions: Row_Number() https://msdn.microsoft.com/en-us/library/ms186734.aspx

  select id, age, accnum, name 
  from 
  (
     select id, age, accnum, name, ROW_NUMBER() Over (Partition By accnum order by age desc) as rn
     from yourtable
  ) a
  where a.rn <= 2

Upvotes: 1

potashin
potashin

Reputation: 44581

You can use row_number():

select accnum
     , age
from ( select accnum
            , age
            , row_number() over(partition by accnum order by age desc) as r
       from table1 as T1) t where r < 3

Upvotes: 1

Related Questions