CODEWITHSUNDEEP
sqldatabaseoracle-databasewindow-functions
Anshu
Anshu

Reputation: 116

Oracle SQL order within partition

I have a view where the results are returned in descending order of a Sequence. One of the columns in the result set is a correlation_id which is same for a bunch of rows. I was hoping to apply a sort within the partition of correlation_id in order of creation timestamp.


Data:
---------------------------
SEQ | CORRELATION_ID | CR_TIMESTAMP
9   | Z              | 22/FEB/16 03:00:19.191000000 PM
8   | Z              | 22/FEB/16 02:00:26.577000000 PM
7   | Z              | 22/FEB/16 01:07:58.171000000 PM
6   | A              | 22/FEB/16 03:07:58.171000000 PM
5   | A              | 22/FEB/16 02:07:58.171000000 PM

What I want is maintain original order, only sort within the partition by CR_TIMESTAMP:
---------------------------
SEQ | CORRELATION_ID | CR_TIMESTAMP                    | SRLNO 
7   | Z              | 22/FEB/16 03:07:58.171000000 PM | 1
8   | Z              | 22/FEB/16 02:00:26.577000000 PM | 2
9   | Z              | 22/FEB/16 01:07:58.171000000 PM | 3
5   | A              | 22/FEB/16 02:07:58.171000000 PM | 1
6   | A              | 22/FEB/16 03:07:58.171000000 PM | 2

I tried (without success) 
select V.*,  ROW_NUMBER()
    OVER (PARTITION BY CORRELATION_ID 
    ORDER BY CR_TIMESTAMP ASC) as SRLNO FROM A_VIEW V;

My attempt resulted as: final results in ascending order of CORRELATION_ID. i.e all A, then all B, then .. all Z. Within each partition, the rank was correctly in the order of CR_TIMESTAMP.

Upvotes: 0

Views: 1819

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Here is how I understand your request: You want CORRELATION_ID Z first, because its highest SEQ (9) is higher than A's highest SEQ (6), but inside each CORRELATION_ID you want your records ordered by date.

select seq, correlation_id, cr_timestamp
from mytable
order by max(seq) over (partition by correlation_id) desc, cr_timestamp desc;

Upvotes: 1

Related Questions