SQL多列排序 [英] Sql Order by on multiple column

问题描述

我得到以下结果

VendorName | IncidentID | IncidentStatus | IncidentDate
-------------------------------------------------------
XYZ        | 100        |     Open       | 02-JUN-2011    
XYZ        | 101        |     Open       | 03-JUN-2011  
ABC        | 102        |     Open       | 01-JUN-2011  
XYZ        | 103        |     Open       | 01-APR-2011  
ABC        | 105        |     Open       | 05-JUN-2011 

我要订购具有最新事件的VendorName.供应商ABC具有最新的事件，因此它应该首先与同一供应商的所有其他事件一起出现，然后是与所有各自的事件按降序排列的下一个供应商.期望的结果是这样的-

I want to order VendorName which has latest incident. Vendor ABC has the latest incident hence it should come first with all other incident for same vendor and then next Vendor with all respective incident in descending order.The desired result is like this -

VendorName | IncidentID | IncidentStatus | IncidentDate  
-------------------------------------------------------
ABC        | 105        |     Open       | 05-JUN-2011 
ABC        | 102        |     Open       | 01-JUN-2011
XYZ        | 101        |     Open       | 03-JUN-2011 
XYZ        | 100        |     Open       | 02-JUN-2011    
XYZ        | 103        |     Open       | 01-APR-2011  

ORDER BY IncidentDate desc, VendorName没有提供所需的输出.有帮助吗?

ORDER BY IncidentDate desc, VendorName doesn't give the desired output. Any help ?

推荐答案

使用分析功能:

SELECT *
FROM(
    SELECT 
        VendorName, 
        IncidentID, 
        IncidentStatus, 
        IncidentDate, 
        MAX(IncidentDate) OVER (PARTITION BY VendorName) maxDate
    FROM yourTable
) t
ORDER BY t.maxDate DESC, t.VendorName ASC, t.IncidentDate DESC

请参阅: http://docs.oracle.com/javadb/10.8.2.2/ref /rrefsqlj13658.html http://docs.oracle.com/cd/E11882_01/server .112/e10592/functions003.htm http://docs.oracle.com/cd/E11882_01/server .112/e26088/functions004.htm

这篇关于SQL多列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！