Re: Very Tricky SELECT statement

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/05/04 

Date: Tue, 6 Apr 2004 01:39:52 +0530

Try:

select a.*
from [order detail] a join
(select orderid, max(orderdate) orderdate
from [order detail]
group by orderid) b
on a.orderid = b.orderid
and a.orderdate = b.orderdate 

-- 
Vishal Parkar
vgparkar@yahoo.co.in

Relevant Pages

  • Re: Insert Into with a row number.
    ... This is an add on to an existing application that supports SQL Server 2000 and 2005 and is currently working on 2008 compatibility. ... information about a detail item that's been order but the key is OrderID and LineNumber. ... Also there is now hard value for the number of rows already in the order detail table. ...
    (comp.databases.ms-sqlserver)
  • Re: Stored procedure and dynamic ORDER BY
    ... WHEN 'OrderID' THEN OrderID ... WHEN 'CustomerID' THEN CustomerID ... WHEN 'OrderDate' THEN OrderDate ... WHEN 'EmployeeID' THEN EmployeeID ...
    (microsoft.public.sqlserver.programming)
  • Re: Clustered vs Non-Clustered
    ... > either leave OrderId as clustered ... > OrderNumber ... > couldn't that be set as the primary key ?. ... >> OrderDate smalldatetime NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: XML data to SQL Server with multiple orders and multiple order det
    ... SQLXML Bulk Load component from SQLXML 3.0. ... inserting the data using a ... OrderDate DATETIME NOT NULL ... --INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) ...
    (microsoft.public.sqlserver.xml)
  • Re: Go to specified row
    ... that some people will probably bash you for the notion of a row number in a ... tie-breaker, e.g., orderid. ... ORDER BY orderdate, orderid ... BG, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)