CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Qasim0787
Qasim0787

Reputation: 301

How to pickup last and second last date using oracle query

I have following table

Table PRODUCT:

PRO_ID   PRO_Date   PRO_Price
123      1/1/2016   2000
123      2/1/2016   2500
123      3/1/2016   1500
123      5/1/2016   3000
456      1/1/2016   2000
456      2/1/2016   2500
456      3/1/2016   1500
456      5/1/2016   3000
456      6/1/2016   3500

Query currently using:

select PRO_ID, max(PRO_DATE) as Last_PRO_Date
from
(select PRO_ID, PRO_DATE from PRODUCT where PRO_DATE<='01-JUN-2016') 
group by PRO_ID

Output currently getting:

PRO_ID   Last_PRO_Date  
123      5/1/2016       
456      6/1/2016

But now I want also pickup second last date available.

Expected Output/ Required Output:

PRO_ID   Last_PRO_Date   Second_Last_PRO_Date
123      5/1/2016        3/1/2016
456      6/1/2016        5/1/2016

Upvotes: 2

Views: 1162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can write your query without a subquery:

select PRO_ID, MAX(PRO_DATE) as Last_PRO_Date
from PRODUCT
where PRO_DATE <= '01-JUN-2016' 
group by PRO_ID;

However, for the previous two dates, I would use dense_rank() and aggregation:

select PRO_ID,
       max(casse when seqnum = 1 then PRO_DATE end) as Last_PRO_Date,
       max(casse when seqnum = 2 then PRO_DATE end) as Second_Last_PRO_Date
from (select PRO_ID, PRO_DATE,
             dense_rank() over (partition by PRO_ID order by PRO_DATE desc) as seqnum
      from PRODUCT
      where PRO_DATE <= '01-JUN-2016' 
     ) t
group by PRO_ID;

Upvotes: 3

Related Questions