We help IT Professionals succeed at work.

Northwind example:get max(qty) for a max(date) < @MyDate

jxharding
jxharding used Ask the Experts™
on
hi,
i please need help to get the highest date for a [Order Details] for Northwind, where date earlier than @MyDate,
and then get the Max(Qty) for that date.
e.g.

-the complete sql query
select  * from dbo.Orders o inner join [Order Details] od on o.orderID = od.orderid
where orderdate < '1997/09/01'


-my attempt gets the Max(OrderDate), but not the max(Qty) for that orderDate
select EmployeeID,Max(OrderDate) as OrderDate from dbo.Orders o inner join [Order Details] od on o.orderID = od.orderid
where orderdate < '1997/09/01'
group by EmployeeID
so it should return
EmployeeID             OrderDate    MaxQty
3                              1997-08-25   21
6                               1997-08-25   21
9                                1997-08-27  35

please help me to get the MaxQty with the MaxDate



   
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
I think you should read this article:
http://www.experts-exchange.com/A_3203.html