Return Top records not Top values?

[Guest]

I totally understand the method for returning Top N values per group as
explained in KB article 210039. I want the last 5 or fewer items bought by
each customer. However, if a customer buys 1 item on Jan 1 and 25 items on
Jan 2, I get 26 records! I just want the first five records. Is there any way
to do that?

My eyeballs are bleeding from reading KB articles and this newsgroup and I
really need to get some sleep now. Can somebody PLEASE help me?

Thanks in advance!
Val

 

[Guest]

SELECT M1.CustID, M1.NumOfItems
FROM CustomerTable AS M1
WHERE M1.NumOfItems In (SELECT Top 5 M2.NumOfItems
FROM CustomerTable as M2
WHERE M2.CustID =M1.CustID
ORDER BY M2.NumOfItems)

That will return the top 5 records for each cust, start with the lowest
number of purchase, you have to change the name of the table and the fields.
If the table return few records per customer per day, then first create a
group by query, that return the total purchase per day, and ten run the above
sql on the query.

 

[John Spencer (MVP)]

Add one more sort to the subquery and use the primary key of the row as the sort.

SELECT TOP ...
FROM ...
ORDER BY purchaseDate Desc, PrimaryKeyField

 

[Guest]

Thank you John. Funny, I was mulling this problem over in the shower today
(where I do my best thinking) and I actually stumbled across this idea
somehow. However, simply sorting on the Parts.ID field isn't doing the trick.
I guess I have to nest another query, similar to Ofer is suggesting I
guess...

Again, I'm awake way too late, so my brain is lagging right now, but this
job is due tomorrow morning so I'm going to keep torturing the query until it
talks. If there are any more alert good samaritans out there, feel free to
have a crack at it:

SELECT [Parts].[CUSTID], [Parts].[Sold Date], [Parts].[PART],
[Parts].[Material], [Parts].[Location]
FROM Customers INNER JOIN Parts ON [Customers].[CUSTID]=[Parts].[CUSTID]
WHERE (((Parts.[Sold Date]) In (SELECT TOP 5 [Sold Date] FROM Parts
WHERE Parts.CustID = Customers.CustID ORDER BY [Sold Date] DESC)))
ORDER BY [Customers].[CO], [Parts].[Sold Date] DESC;

According to what I think I know, I need to get the top 5 [Parts].[PART]
AFTER I get the top 5 by CustID and Sold Date.

Thanks!
Val

 

[Guest]

Ever notice how we sometimes just try to make things a little too difficult?
I finally found my answer. It might not be the most efficient method, but by
golly it works:

PartsSubQry:
SELECT PARTS.ID, PARTS.[Sold Date], PARTS.CUSTID, PARTS.PART,
PARTS.Material, PARTS.Location, [Sold Date] & [ID] AS DatePart
FROM PARTS
ORDER BY PARTS.[Sold Date] DESC;

SQL Statement for PartsSubReport:
SELECT PartsSubQry.ID, PartsSubQry.CUSTID, PartsSubQry.[Sold Date],
PartsSubQry.PART, PartsSubQry.Material, PartsSubQry.Location,
PartsSubQry.DatePart
FROM Customers INNER JOIN PartsSubQry ON Customers.CUSTID = PartsSubQry.CUSTID
WHERE (((PartsSubQry.DatePart) In (SELECT TOP 5 DatePart FROM PartsSubQry
WHERE PartsSubQry.CustID = Customers.CustID ORDER BY DatePart DESC)))
ORDER BY Customers.CO, [Parts].[Sold Date] DESC;