CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
bobroxsox
bobroxsox

Reputation: 992

"Group" some rows together before sorting (Oracle)

I'm using Oracle Database 11g. I have a query that selects, among other things, an ID and a date from a table. Basically, what I want to do is keep the rows that have the same ID together, and then sort those "groups" of rows by the most recent date in the "group".

So if my original result was this:

ID   Date
3    11/26/11
1    1/5/12
2    6/3/13
2    10/15/13
1    7/5/13

The output I'm hoping for is:

ID   Date
3    11/26/11     <-- (Using this date for "group" ID = 3)
1    1/5/12
1    7/5/13       <-- (Using this date for "group" ID = 1)
2    6/3/13
2    10/15/13     <-- (Using this date for "group" ID = 2)

Is there any way to do this?

Upvotes: 1

Views: 804

Answers (3)

schurik
schurik

Reputation: 7928

Here is the query using analytic functions:

select 
  id
, date_
, max(date_) over (partition by id) as max_date
  from table_name
  order by max_date, id 
;

Upvotes: 1

spencer7593
spencer7593

Reputation: 108530

One way to get this is by using analytic functions; I don't have an example of that handy.

This is another way to get the specified result, without using an analytic function (this is ordering first by the most_recent_date for each ID, then by ID, then by Date):

SELECT t.ID
     , t.Date
  FROM mytable t
  JOIN ( SELECT s.ID
              , MAX(s.Date) AS most_recent_date
           FROM mytable s
          WHERE s.Date IS NOT NULL
          GROUP BY s.ID
       ) r
    ON r.ID = t.ID
 ORDER
    BY r.most_recent_date
     , t.ID
     , t.Date

The "trick" here is to return "most_recent_date" for each ID, and then join that to each row. The result can be ordered by that first, then by whatever else.

(I also think there's a way to get this same ordering using Analytic functions, but I don't have an example of that handy.)

Upvotes: 2

WoMo
WoMo

Reputation: 7266

You can use the MAX ... KEEP function with your aggregate to create your sort key:

with 
  sample_data as
   (select 3 id, to_date('11/26/11','MM/DD/RR') date_col from dual union all
    select 1,  to_date('1/5/12','MM/DD/RR') date_col from dual union all
    select 2, to_date('6/3/13','MM/DD/RR') date_col from dual union all
    select 2, to_date('10/15/13','MM/DD/RR') date_col from dual union all
    select 1, to_date('7/5/13','MM/DD/RR') date_col from dual)
select 
  id,
  date_col,
  -- For illustration purposes, does not need to be selected:
  max(date_col) keep (dense_rank last order by date_col) over (partition by id) sort_key
from sample_data
order by max(date_col) keep (dense_rank last order by date_col) over (partition by id);

Upvotes: 1

Related Questions