Can anyone explain....

[Graham T]

Whilst looking for a solution to a problem today, I came across a couple of topics by Pat H and Jon K and managed to cobble together the following:

Select OrderNum, OrderDate
FROM Order_Lines AS ol
WHERE (SELECT COUNT(*) FROM Order_Lines
	WHERE UserID = ol.UserID AND OrderDate > ol.OrderDate) + 1 < = 2

Now this appear to do what is required.

It returns the top 2 most recent orders for each UserID. But how it does this is the part I don't understand.

If anyone can throw some light on it I would apprieciate the response.

TIA

Graham

 

[Jon K]

The Subquery expression:-

(SELECT COUNT(*) FROM Order_Lines WHERE UserID = ol.UserID AND OrderDate > ol.OrderDate) + 1

ranks the records of each UserID as 1,2,3,4,etc. starting from the most recent date.

Then the <= 2 returns those records ranked with the number 1 or 2 (hence the top 2 most recent orders for each UserID.)


You can run this query to see how the records of each UserID are ranked:-

SELECT UserID, OrderDate, OrderNum,
(SELECT COUNT(*) FROM Order_Lines WHERE UserID = ol.UserID AND OrderDate > ol.OrderDate) + 1 AS Rank
FROM Order_Lines AS ol
ORDER BY UserID, OrderDate DESC;


Hope it helps.

 

[Graham T]

Thanks Jon

Much appreciated.

Graham