Ranking over several columns

Question

In the process of query optimization I got to following SQL query:

select s.*
from
(
select id, DATA, update_dt, inspection_dt, check_dt
RANK OVER() 
(PARTITION by ID
 ORDER BY update_dt DESC, DATA) rank
 FROM TABLE 
where update_dt < inspection_dt or update_dt < check_dt 
) r
where r.rank = 1

Query returns the DATA that corresponds to the latest check_dt. However, what I want to get is: 1. DATA corresponding to latest check_dt 2. DATA corresponding to latest inspection_dt.

One of the trivial solutions - just write two separate queries with a where single condition - one for inspection_dt, and one for check_dt. However, that way it loses initial intent - to shorten the running time.

By observing the source data I noticed the way to implement it - check date is always later than inspection date; knowing that I could just extract the record with the rank = 1 and it will give me DATA corresponding to latest CHECK_DT, and record with the largest rank would correspond to INSPECTION. However, data I'm afraid data will not be always consistent, so I was looking for more abstract solution.

score 1 · Answer 1 · answered Jun 25 '14 at 04:23

How about this?

select s.*
from (select id, DATA, update_dt, inspection_dt, check_dt,
             RANK() OVER (PARTITION by ID
                          ORDER BY update_dt DESC, DATA
                         ) as rank_upd,
             RANK() OVER (PARTITION by ID
                          ORDER BY inspection_dt DESC, DATA
                         ) as rank_insp,

     FROM TABLE 
    ) r
where r.rank_upd = 1 or r.rank_insp = 1;

Ranking over several columns

1 Answers1