SQL Server SELECT only the rows with MAX DATE

Question

I have a table of data  the db is MSSQL        ID  OrderNO PartCode  Quantity DateEntered     417 2144     44917    100      18-08-11     418 7235     11762    5        18-08-11     419 9999     60657    100      18-08-11     420 9999     60657    90       19-08-11   I would like to make a query that returns OrderNO  PartCode and Quantity  but only for the last registered order   From the example table I would like to get back the following info        OrderNO PartCode  Quantity           2144     44917    100            7235     11762    5              9999     60657    90         Notice that only one line was returned for order 9999   Thanks

User · Answer

And u can also use that select statement as left join query... Example :

... left join (select OrderNO,
   PartCode,
   Quantity from (select OrderNO,
         PartCode,
         Quantity,
         row_number() over(partition by OrderNO order by DateEntered desc) as rn
  from YourTable) as T where rn = 1 ) RESULT on ....

Hope this help someone that search for this :)

User · Answer

If you have indexed ID and OrderNo You can use IN   I hate trading simplicity for obscurity  just to save some cycles    select   from myTab where ID in select max ID  from myTab group by OrderNo

User · Answer

The best way is Mikael Eriksson  if ROW NUMBER   is available to you   The next best is to join on a query  as per Cularis  answer   Alternatively  the most simple and straight forward way is a correlated-sub-query in the WHERE clause   SELECT     FROM   yourTable AS  data  WHERE   DateEntered    SELECT MAX DateEntered  FROM yourTable WHERE orderNo    data  orderNo    Or     WHERE   ID    SELECT TOP 1 ID FROM yourTable WHERE orderNo    data  orderNo ORDER BY DateEntered DESC

User · Answer

Try to avoid IN use JOIN  SELECT SQL CALC FOUND ROWS    FROM  SELECT  msisdn  callid  Change color  play file name  date played FROM insert log    WHERE play file name NOT IN  Prompt1   Conclusion Prompt 1   silent    ORDER BY callid ASC  t1 JOIN  SELECT MAX date played  AS date played FROM insert log GROUP BY callid  t2 ON t1 date played t2 date played

User · Answer

For MySql you can do something like the following   select OrderNO  PartCode  Quantity from table a join  select ID  MAX DateEntered  from table group by OrderNO  b on a ID   b ID

User · Answer

rownumber   over      is working but I didn t like this solution for 2 reasons   - This function is not available when you using older version of SQL like SQL2000  - Dependency on function and is not really readable   Another solution is   SELECT tmpall  OrderNO           tmpall  PartCode           tmpall  Quantity    FROM    SELECT  OrderNO                   PartCode                   Quantity                   DateEntered          FROM   you table  AS tmpall        INNER JOIN  SELECT  OrderNO                             Max  DateEntered   AS  max date                    FROM   your table                    GROUP  BY OrderNO   AS tmplast                ON tmpall  OrderNO    tmplast  OrderNO                    AND tmpall  DateEntered    tmplast  max date

User · Answer

This works for me  use MAX CONVERT date  ReportDate   to make sure you have date value   select max  CONVERT date  ReportDate   FROM  TraxHistory

User · Answer

select OrderNo PartCode Quantity from dbo Test t1 WHERE EXISTS SELECT 1          FROM dbo Test t2          WHERE t2 OrderNo   t1 OrderNo            AND t2 PartCode   t1 PartCode          GROUP BY t2 OrderNo                    t2 PartCode          HAVING t1 DateEntered   MAX t2 DateEntered     This is the fastest of all the queries supplied above  The query cost came in at 0 0070668   The preferred answer above  by Mikael Eriksson  has a query cost of 0 0146625  You may not care about the performance for such a small sample  but in large queries  it all adds up

User · Answer

SELECT t1 OrderNo  t1 PartCode  t1 Quantity FROM table AS t1 INNER JOIN  SELECT OrderNo  MAX DateEntered  AS MaxDate             FROM table             GROUP BY OrderNo  AS t2 ON  t1 OrderNo   t2 OrderNo AND t1 DateEntered   t2 MaxDate    The inner query selects all OrderNo with their maximum date  To get the other columns of the table  you can join them on OrderNo and the MaxDate

User · Answer

This worked for me perfectly fine      select name  orderno from            select name  orderno  row number   over partition by             orderno order by created date desc  as rn from orders       O where rn  1

User · Answer

If rownumber   over      is available for you       select OrderNO         PartCode         Quantity from  select OrderNO               PartCode               Quantity               row number   over partition by OrderNO order by DateEntered desc  as rn       from YourTable  as T where rn   1

[sql-server] SQL Server: SELECT only the rows with MAX(DATE)

Examples related to sql-server