SQL多列排序 | incident

本文介绍了SQL多列排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我得到以下结果

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多列排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！