SQL 多列上的Sql Order by
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13897024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Order by on multiple column
提问by Pankaj
I've the below result
我有以下结果
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
I want to order VendorNamewhich has latest incident. Vendor ABChas 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最新的事件。供应商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, VendorNamedoesn't give the desired output. Any help ?
ORDER BY IncidentDate desc, VendorName没有给出所需的输出。有什么帮助吗？
回答by Roger
Use analytic functions:
使用解析函数：
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
Refer to: http://docs.oracle.com/javadb/10.8.2.2/ref/rrefsqlj13658.htmlhttp://docs.oracle.com/cd/E11882_01/server.112/e10592/functions003.htmhttp://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm
参考： 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
回答by David Aldridge
This will do it ...
这将做到...
ORDER BY MAX(INCIDENTDATE) OVER (PARTITION BY VENDORNAME) DESC, INCIDENTDATE DESC
... but I'm not sure if the analytic function is allowed in the ORDER BY. If it isn't, calculate it in a subquery and order by in the main query ...
...但我不确定 ORDER BY 中是否允许使用解析函数。如果不是，则在子查询中计算它并在主查询中排序...
select ...
from (
select Max(incidentdate) over (partition by vendorname) max_incidentdate_by_vendor,
...)
order by max_incidentdate_by_vender desc, incidentdate desc
回答by user10054759
If you are on a RAC installation
如果您使用的是 RAC 安装
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
a.INST_ID,
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024/1024) AS SIZE_GB
FROM gv$log a
JOIN gv$logfile b ON a.Group#=b.Group#
AND a.INST_ID=b.INST_ID
ORDER BY a.INST_ID ASC, a.GROUP# ASC;
回答by satprem rath
select vendorname, incidentid, incidentstatus, incidentdate, max(incidentdate)
over (partition by vendorname order by incidentdate desc) max_incidentdate
from t1 order by max_incidentdate desc, incidentdate desc
