Select Distinct Top # question

[avu]

Hello,

Can someone please help me with this question? Here's a sample of the data:

clientId orderDate orderQuantity
1 6/15/2004 2000
1 6/14/2004 1500
2 6/16/2004 350
2 6/8/2004 1200
2 6/7/2004 1000
3 6/16/2004 500
3 6/15/2004 1500
4 6/16/2004 1300
4 6/13/2004 500
4 6/12/2004 1500
4 6/11/2004 3000
4 6/10/2004 1200


Want: a query to calculate sum of "orderQuantity" of clientId 2 and 4, for distinct "orderDate" of last (most current) 3 dates.

That is, the query will do:

- orderDate = 6/16/2004 -> 350 (of clientId 2) + 1300 (of clientId 4)
- orderDate = 6/13/2004 -> 500 (of clientId 4)
- orderDate = 6/8/2004 -> 1200 (of clientId 2)

The result will show:
orderDate orderQuantitySUM
6/16/2004 1650
6/13/2004 500
6/8/2004 1200

I've tried:

SELECT clientId, orderDate, SUM(orderQuantity) AS sumQuantity
FROM myTable t1
WHERE (orderDate IN
(SELECT DISTINCT TOP 3 t2.orderDate FROM myTable t2
WHERE (t2.clientId = t1.clientId) ORDER BY t2.orderDate DESC))
GROUP BY clientId, orderDate
HAVING (clientId IN (2, 4))
ORDER BY clientId, orderDate DESC

But this didn't seem to do the job.

Can you point out what's wrong with my query?

Thanks much.

avu

 

[SQLBill]

First thing I notice is:

SELECT clientId, orderDate, SUM(orderQuantity) AS sumQuantity

Why is clientId in there? Your description didn't say you wanted that returned.

This SELECT will return ALL orderDate rows instead of the DISTINCT ones.

Change it to:

SELECT DISTINCT orderDate, SUM(orderQuantity) AS sumQuantity

Let us know how that worked.

Also, have you tested your sub-Select to make sure it returns what you expected?

Test your query this way....run the sub-select. Take the results and put them in the WHERE clause. Then run the outer SELECT. Where are you not getting the proper results?

-SQLBill

 

[ESquared]

6/16/2004, 6/13/2004, and 6/8/2004 aren't the most current 3 dates for those two clients. Are you sure you described it right?

Does OrderDate ever have a time as well? Or is it always 12:00 am for each value in that column?

 

[SQLBill]

And is OrderDate a DATETIME column?

-SQLBill