Milan Batica Milan Batica - 11 months ago 71
SQL Question

Oracle, get top row using order by desc and rownum

This query gives me invalid identifier error, and i know it is because subquery will only be able to access data that is one layer higher.

select *
from t2_callerid_plan cp
where cp.subsrefnum in (
select *
from (
select vsap.subsrefnum
from prv_internet_responses_vsap vsap
where vsap.subsrefnum = cp.subsrefnum
order by desc
where rownum = 1

Now, i was wandering if there is way i can create query that would be able to return only the newest row while using the data from query in subquery?

Answer Source

You can use ROW_NUMBER() :

    SELECT cp.*,
           ROW_NUMBER() OVER(PARTITION BY cp.subsrefnum ORDER BY desc) as rnk
    from t2_callerid_plan cp
    JOIN  prv_internet_responses_vsap vsap
     ON vsap.subsrefnum = cp.subsrefnum) p
WHERE p.rnk = 1