Latest Date in a group function

[sarah2855]

I have a table that looks like this:
CustomerID ProductOrdered OrderDate OrderQuantity
0001 ProdA 1/1/2007 3
0001 ProdA 1/1/2008 2
0002 ProdB 1/1/2006 1


I'm only interested the LATEST order of each customer. I'm trying to use SELECT TOP, but somehow it doesn't work with Group function:

SELECT TOP 1 CustomerID,ProductOrdered,OrderQuantity, MAX(OrderDate) from CustomerTable
group by CustomerID,ProductOrdered,OrderQuantity

Please advise
Thanks in advance

[patjones]

The TOP 1 feature will only grab the very first record from your table.

You could try something like this:

SELECT * 
FROM CustomerTable INNER JOIN (SELECT CustomerID, MAX(OrderDate) AS latest_order_date
                               FROM CustomerTable
                               GROUP BY CustomerID) AS tblMax ON (CustomerTable.CustomerID = tblMax.CustomerID AND CustomerTable.OrderDate = tblMax.latest_order_date);

Pat

[patjones]

I would also add that you should make sure that CustomerID and OrderDate are indexed columns.

Pat

[sarah2855]

I have a table that looks like this:
CustomerID ProductOrdered OrderDate OrderQuantity
0001 ProdA 1/1/2007 3
0001 ProdA 1/1/2008 2
0002 ProdB 1/1/2006 1


I'm only interested the LATEST order of each customer. I'm trying to use SELECT TOP, but somehow it doesn't work with Group function:

SELECT TOP 1 CustomerID,ProductOrdered,OrderQuantity, MAX(OrderDate) from CustomerTable
group by CustomerID,ProductOrdered,OrderQuantity

Please advise
Thanks in advance

@ Pat: thanks Pat, your solution worked perfectly fine. They are not indexed columns but it's a small database. do you think it's gonna be an issue?

[patjones]

It is generally good practice to do joins on indexed columns, and since it doesn't really take much effort to go into table design view and do it, I would certainly make the change.

Pat