SQL MAX Order Number (DATE)

Question

I have the following query:

SELECT o.ClientId, o.MAX(Date), o.OrderNumber FROM dbo.tblOrders GROUP BY o.ClientId, o.OrderNumber 

This still gives me all the serial numbers, as if to get only the order number that comes with the MAX date.

What I'm trying to accomplish is getting the MAX order date for each customer and displaying the order number.

thanks

Answer 1

 SELECT t.ClientId, t.MaxDate, o.OrderNumber FROM (SELECT ClientId, MAX(Date) as MaxDate FROM dbo.tblOrders GROUP BY ClientId) t INNER JOIN dbo.tblOrders o ON t.ClientId = o.ClientId AND t.MaxDate = o.Date 

If you are using an RDBMS that supports windowing functions, such as SQL Server 2005+, this can also be done as follows:

 SELECT t.ClientId, t.OrderNumber, t.Date FROM (SELECT ClientId, OrderNumber, Date, ROW_NUMBER() OVER(PARTITION BY ClientId ORDER BY Date DESC) as RowNum FROM dbo.tblOrders ) t WHERE t.RowNum = 1 

Answer 2

Look at the HAVING clause in SQL or just add a WHERE clause based on order_id with a subquery that gets order_id in the table with the corresponding MAX value.

Answer 3

 SELECT o.ClientID, o.Date, o.OrderNumber FROM dbo.tblOrders o INNER JOIN (SELECT o1.ClientId, MAX(o1.Date) as MaxDate, o1.OrderNumber FROM dbo.tblOrders o1 GROUP BY o.ClientId) o2 ON o.ClientID = o2.ClientID AND o.Date = o2.MaxDate