Top 5 values per customer 1

[GPM4663]

Hi,
I know this has been asked many times on threads but each one I read I don't understand what I have to do. I have copied the code below which I can relate to my own except I only have one table that I'm trying to get the values from i.e. t1. In this table I have want to get the top 5 values for each customer. Any further pointers would be great.

SELECT t1.*
FROM [MyTable] t1
WHERE t1.OrderDate In (select top 5 t2.OrderDate from [MyTable] t2 where t2.CustomerID = t1.CustomerID ORDER BY t2.OrderDate DESC);


Many thanks

GPM

 

[alvechurchdata]

If you want the top five from a single table try:

Select Top 5 CompanyName from Customer Order By CompanyName

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[GPM4663]

Hi Alvechurchdata,
Thanks for the reply but I need the top 5 [OrderQuantities] from EACH customer. I have 12 customers therefore I should get 60 results in total. Any further thoughts?

Thanks again,

Graham

 

[alvechurchdata]

Thanks for the reply but I need the top 5 [OrderQuantities] from EACH customer.

A little more complicated, you need to use a subquery. I've done a example based on the NorthWind sample database:

SELECT Customers.CustomerID, Orders.OrderID, Orders.Freight
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE OrderID IN
(select top 5 orderid from orders where orders.customerid=customers.customerid order by freight desc)
ORDER BY Customers.CustomerID, Orders.Freight Desc

This gets the top 5 [Freight] values for each customer by running a subquery for each customer. I put it together by building the main query in the Query Builder then switching to SQL view for the subquery. Hope you can transfer the technology to your own table and fieldnames.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

GPM, what's wrong with the query you've posted ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GPM4663]

HI PH and Alvechurchdata,
There was nothing wrong with the query I posted, I had gotten it from another thread and didn't understand it. It was the "In" command with the subquery that confused me. I have the northwind database now and should be able to work something out. Thanks to both you guys for the advice!!

Thanks again,

GPM