Menu
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 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 doesn't give the desired output. Any help ?
876
You can also ORDER BY two or more columns, which creates a nested sort . The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence. The following query and Figure 3 and the corresponding query results show nested sorts.
Can you arrange the result set of an SQL query on multiple columns? If you specify multiple columns, the result set is sorted by the first column and then that sorted result set is sorted by the second column, and so on.
The ORDER BY clause applies to the final result set from the FULLSELECT statement. You can sort on either a single column or multiple columns. To sort on columns in a FULLSELECT statement that have the same ordinal value but are in different subordinate SELECT statements, the columns must have the same name.
customers ORDER BY 1, 2; In this example, 1 means the first_name column and 2 means the last_name column. Using the ordinal positions of columns in the ORDER BY clause is considered as bad programming practice for a couple of reasons.
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.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
109
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 ...
select ...
from (
select Max(incidentdate) over (partition by vendorname) max_incidentdate_by_vendor,
...)
order by max_incidentdate_by_vender desc, incidentdate desc
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
