Selectint Top N within a group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mar - 29 Jun 2008 08:28 GMT

I have a table that looks like this:

ACCOUNT    ID    DATE
aaa        01/01/08
aaa        02/01/08
aaa        03/01/08
aaa        04/01/08
bbb        02/01/08
bbb        03/01/08
bbb        04/01/08
bbb        05/01/08

I need a query that sorts it by ACCOUNTID then by DATE in descending
order and then only returns the top 3 for each account. It would
return the following:

ACCOUNT    ID    DATE    NUMBER
aaa        04/01/08    1
aaa        03/01/08    2
aaa        02/01/08    3
bbb        05/01/08    1
bbb        04/01/08    2
bbb        03/01/08    3

Help?

Thanks.

Reply to this Message

Uri Dimant - 29 Jun 2008 08:41 GMT

mar
with cte

as

(

select * ,row_number()

over (partition by account order by datename desc)as r

from #t

) select * from cte where r<=3

>I have a table that looks like this:
>
[quoted text clipped - 23 lines]
>
> Thanks.

Reply to this Message