Selecting Top N within a group

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mar - 29 Jun 2008 08:33 GMT

I have a table that looks like this:

ACCOUNTID    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:

ACCOUNTID    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

Can you help with this query?

Thanks.

Reply to this Message

Tom Moreau - 30 Jun 2008 14:01 GMT

Try:

;with x
as
(
select
ACCOUNTID
, [DATE]
, row_number () over (partition by ACCOUNTID order by ACCOUNTID, [DATE]
desc) as NUMBER
from
MyTable
)
select
ACCOUNTID
, [DATE]
, NUMBER
from
x
where
NUMBER between 1 and 3
order by
ACCOUNTID
, [DATE] desc

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

I have a table that looks like this:

ACCOUNTID 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:

ACCOUNTID 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

Can you help with this query?

Thanks.

Reply to this Message

mar - 02 Jul 2008 19:59 GMT

I'm not sure I understand the ";with x"... how do I plug this in to a
sql query?
Thanks!
mar.

> Try:
>
[quoted text clipped - 56 lines]
>
> Thanks.

Reply to this Message

Tom Moreau - 02 Jul 2008 22:43 GMT

It's how you do a Common Table Expression (CTE) in SQL Server 2005. Just
cut and paste.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

I'm not sure I understand the ";with x"... how do I plug this in to a
sql query?
Thanks!
mar.

On Jun 30, 6:01 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:

> Try:
>
[quoted text clipped - 59 lines]
>
> Thanks.

Reply to this Message

mar - 03 Jul 2008 01:09 GMT

I forgot to mention I'm on SQL 2000. I copied and pasted but the
expression gets errors on that and on row_number:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Server: Msg 195, Level 15, State 1, Line 7
'row_number' is not a recognized function name.

> It's how you do a Common Table Expression (CTE) in SQL Server 2005. Just
> cut and paste.
[quoted text clipped - 79 lines]
>
> - Show quoted text -

Reply to this Message

Tom Moreau - 03 Jul 2008 13:19 GMT

It's important that you do mention which version of SQL Server you are
using. SQL 2005 is current and we usually assume that you are using it,
unless you specify otherwise. Here's an example from Northwind that takes
the 3 most-recent orders for each customer:

select
*
from
Orders o1
where
3 >
(
select
count (*)
from
Orders o2
where
o2.CustomerID = o1.CustomerID
and (o2.OrderDate > o1.OrderDate
or (o2.OrderDate = o1.OrderDate
and o2.OrderID > o1.OrderID))
)
order by
o1.CustomerID
, o1.OrderDate desc

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

> It's how you do a Common Table Expression (CTE) in SQL Server 2005. Just
> cut and paste.
[quoted text clipped - 80 lines]
>
> - Show quoted text -

Reply to this Message

mar - 03 Jul 2008 17:38 GMT

My bad on the SQL version.

THANK YOU! I got it working on my tables!

mar.

> It's important that you do mention which version of SQL Server you are
> using. SQL 2005 is current and we usually assume that you are using it,
[quoted text clipped - 126 lines]
>
> - Show quoted text -

Reply to this Message