sql-server-2008 - 在 sql server 2008 中使用 order by 获取唯一记录

Question

ID    OrderId  status
    1  12       1
    2  13       1
    3  14       2
    4  12       3
    5  15       1
    6  14       1

大家好，

尝试为上面的表构建 sql 查询以获得如下结果:

   OrderId  status
         12       3
         13       1
         14       1
         15       1

我想要带有最新状态代码的唯一 orderid

我试过这个查询

Select Distinct OrderID 
from OrdersStatusHistory 
order by Status desc

获取记录但有重复的 orderid

Select OrderID, Status 
from Orders_Status_History 
order by Status desc

但我收到一个错误:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Answer 1

select OrdersStatusHistory.* from OrdersStatusHistory
JOIN
 (select OrderId, max(id) maxId 
      from OrdersStatusHistory 
      group by OrderId) t1
 on OrdersStatusHistory.Id=t1.maxId
 order by OrdersStatusHistory.OrderId

SQLFiddle demo

或者如果您需要突出 OrdeId 的最大状态

select OrderId,max(status)
from OrdersStatusHistory
group by OrderID
order by OrderId