SELECT TOP 3 Users.UserID, Users.Username, Orders.OrderedUnits, Orders.OrderDate
FROM         Users INNER JOIN
                      Orders ON Users.UserID = Orders.UserID
WHERE     (DATEDIFF(day, Matches.MatchDate, GETDATE()) <= 7)
ORDER BY Orders.OrderedUnits

quote:

---

Originally posted by Heinz23

Hi all,
I have a user table and a quantity table. Each user orders some products. Now I want to display the top 3 buyers from the last 7 days. Mainly it's easy but my problem: A user might have 2 records within the last 7 days, one from friday and the other from tuesday. If both would be in the top 3 I only want to see it once (but NOT added together).

Example (Username - Ordered units - Order date):
User A - 400 units - 1. Jan
User B - 350 units - 1. Jan
User C - 200 units - 1. Jan
User A - 500 units - 4. Jan
User C - 250 units - 4. Jan
User D - 300 units - 5. Jan

The query on 5. Jan should return the following top 3:
User A - 500 units
User B - 350 units
User D - 300 units

So orders of User A (400 + 500) and also User C (200 + 250) are NOT summed up so User C should not appear in the list even though he has ordered more units than e.g. User B (450 <-> 350).

My current script:

SELECT TOP 3 Users.UserID, Users.Username, Max(Orders.OrderedUnits), Orders.OrderDate
FROM   Users INNER JOIN Orders ON Users.UserID = Orders.UserID
WHERE     (DATEDIFF(day, Matches.MatchDate, GETDATE()) <= 7)
Group by Users.UserID,Users.Username, Orders.OrderDate
ORDER BY Orders.OrderedUnits 

Unfortunately the script returns:
User A - 500 units
User A - 400 units
User B - 350 units

How could I modify the script so user A is only used once? Many thanks!

---

DECLARE	@Orders TABLE (OrderID INT, OrderDate DATETIME)

SET DATEFORMAT DMY

INSERT	@Orders
SELECT	123, '14.07.2008' UNION ALL
SELECT	456, '12.07.2008' UNION ALL
SELECT	789, '02.07.2008'

DECLARE	@Units TABLE (OrderID INT, CustomerName VARCHAR(20), OrderedUnits INT)

INSERT	@Units
SELECT	123, 'CustomerA', 300 UNION ALL
SELECT	123, 'CustomerB', 230 UNION ALL
SELECT	123, 'CustomerC', 350 UNION ALL
SELECT	456, 'CustomerB', 250 UNION ALL
SELECT	456, 'CustomerC', 350 UNION ALL
SELECT	456, 'CustomerD', 400 UNION ALL
SELECT	789, 'CustomerE', 800

SELECT		TOP 3
		CustomerName,
		OrderedUnits,
		OrderDate,
		OrderID
FROM		(
			SELECT		u.CustomerName,
					u.OrderedUnits,
					o.OrderDate,
					o.OrderID,
					ROW_NUMBER() OVER (PARTITION BY u.CustomerName ORDER BY u.OrderedUnits DESC) AS RecID
			FROM		@Units AS u
			INNER JOIN	@Orders AS o ON o.OrderID = u.OrderID
			WHERE		o.OrderDate >= DATEDIFF(DAY, '19000107', GETDATE())
					AND o.OrderDate < DATEDIFF(DAY, '18991231', GETDATE())
		) AS x
WHERE		RecID = 1
ORDER BY	OrderedUnits DESC