Selecting Top 5 records per each client id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Selecting Top 5 records per each client id

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-05 : 07:22:35

Carla writes "I need a query that will return the top 5 records for each client ID. I have only been able to get a return of the top 5 for the group on a whole.

Thank you for any help you can provide.

Carla"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-05 : 07:52:52

First, I assume you mean "top rows when ordered by a certain field", like date. You obviously need some way to designate the "top" rows.

I'll assume you want the largest 3 invoices per client:

This gives the ranking of each invoice per client:


SELECT 
  clientID, InvoiceNumber, InvoiceDate, InvoiceAmt, 
    (SELECT COUNT(*) 
     FROM Invoices 
     WHERE
       ClientID = I.ClientID AND
       InvoiceAmount <= I.InvoiceAmount) as Rank
FROM
  Invoices I

We just do a query of the above, and we are good to go:


SELECT * FROM
  (above SQL) A
WHERE Rank <= 5
ORDER BY ClientID, Rank

Note that if there is a tie, potentially more than 5 rows will be returned for each clientID. You will need a rule if you wish for no more than 5 to ever be returned. YOu need to logically make a rule, let's say: if there is a tie, than the invoice with the earliest date ranks first.

If so, then the first SQL statement becomes:


SELECT 
  clientID, InvoiceNumber, InvoiceAmt, 
    (SELECT COUNT(*) 
     FROM Invoices 
     WHERE
       ClientID = I.ClientID AND
       (InvoiceAmount <= I.InvoiceAmount) OR
       (InvoiceAmount = I.INvoiceAmount AND
        InvoiceDate <= I.InvoiceDate)) as Rank
FROM
  Invoices I

I hope this leads you down the right trail. This can be tricky but you can learn a whole bunch of new techniques ....

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-05 : 07:57:10

Nice job.

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-09 : 07:39:41

SELECT clientID,
InvoiceNumber,
InvoiceDate,
InvoiceAmt
FROM Invoices A
WHERE InvoiceNumber IN (

SELECT TOP 5 InvoiceNumber
FROM Invoices B
WHERE A.clientID=B.clientID
ORDER BY InvoiceDate
-- If required u can u InvoiceAmt instead of Date but that depeneds on ur requirement
)

V.Ganesh
NetAssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-09 : 11:39:01

That's actually an interesting technique, but it can be really inefficient ...

Translate the two for Northwind and look at the difference in execution plans; the first is 10 times faster than the second:


select customerID, orderID, OrderDate, Rank
from
(
select customerID, orderID, OrderDate,
 (select count(*) from orders b where a.customerID = b.customerID and a.OrderDate <= b.orderDate) as Rank
from
orders a
)
a
where Rank <=5

SELECT customerID, 
OrderID, OrderDate
FROM ORders A
WHERE OrderID IN (

SELECT TOP 5 OrderID
FROM Orders B
WHERE A.customerID=B.customerID
ORDER BY ORderDate
)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources