SQL MAX(DATE) Order Number

I have the following query:

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

This is still giving me all order numbers, how would I only get the order number that goes with the MAX date.

What I am trying to accomplish is getting the MAX order date for each client and showing 开发者_如何学JAVAwhat order number that is.

Thanks

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're using an RDBMS that supports windowing functions, like SQL Server 2005+, this could also be done like this instead:

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

Look at the HAVING clause in SQL or just add a WHERE clause based on order_id with a subselect which gets the order_id on the table with the appropiate MAX value.

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