SQL Server:只选择具有MAX(日期)的行

I have a table of data (the db is MSSQL):

我有一个数据表(db是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.

我想做一个查询，返回OrderNO、PartCode和Quantity，但只返回最后注册的订单。

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.

注意，订单9999只返回一行。

Thanks!

谢谢!

9 个解决方案

#1

119

If rownumber() over(...) is available for you ....

如果rownumber()(…)可供你....

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

#2

The best way is Mikael Eriksson, if ROW_NUMBER() is available to you.

最好的方法是Mikael Eriksson，如果您可以使用ROW_NUMBER()。

The next best is to join on a query, as per Cularis' answer.

下一个最佳选择是按照Cularis的回答加入查询。

Alternatively, the most simple and straight forward way is a correlated-sub-query in the WHERE clause.

或者，最简单、最直接的方法是WHERE子句中的相关子查询。

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)

#3

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.

这是上面提供的所有查询中最快的。查询成本为0.0070668。

The preferred answer above, by Mikael Eriksson, has a query cost of 0.0146625

上面的首选答案是Mikael Eriksson，查询成本为0.0146625

You may not care about the performance for such a small sample, but in large queries, it all adds up.

您可能不关心如此小的示例的性能，但是在大型查询中，所有这些都是有用的。

#4

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.

内部查询选择具有最大日期的所有OrderNo。要获取表的其他列，可以在OrderNo和MaxDate上联接它们。

#5

For MySql you can do something like the following:

对于MySql，您可以执行以下操作:

select OrderNO, PartCode, Quantity from table a
join (select ID, MAX(DateEntered) from table group by OrderNO) b on a.ID = b.ID

#6

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

u也可以使用select语句作为左连接查询……例子:

... 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 :)

希望这能帮助寻找这个的人:)

#7

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.

rownumber() over(…)正在工作，但我不喜欢这个解决方案有两个原因。当您使用像SQL2000这样的老版本的SQL时，这个函数是不可用的，它依赖于函数，而且可读性不强。

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

#8

If you have indexed ID and OrderNo You can use IN: (I hate trading simplicity for obscurity, just to save some cycles):

如果你有索引ID和OrderNo，你可以使用它:(我讨厌为晦涩而交易简单，只是为了节省一些周期):

select * from myTab where ID in(select max(ID) from myTab group by OrderNo);

#9

Try to avoid IN use JOIN

尽量避免使用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

#1

119