SQL多列排序

Question

我得到以下结果

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具有最新的事件，因此应该首先与同一供应商的所有其他事件一起发生，然后是所有事件均按降序排列的下一个供应商。期望的结果是这样的-

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没有给出期望的输出。有什么帮助吗？

Answer 1

使用分析功能：

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