CODEWITHSUNDEEP
oracle-databasegreatest-n-per-group
mlin
mlin

Reputation: 31

Oracle SQL Return Most Recent and Next Most Recent Price and Date

I have list of IDs with prices and dates (multiple occurrences of the same part number for different dates) and would like to return the most recent price date and the second most recent price date. I would like to have the price and dates separated by columns meaning I need to have only one occurrence of an ID.

For example (Part Number and Price are Number and PriceDate and Price End Date are DATES in Month, Day, Year format)

ID  Price PriceDate PriceEndDate
1   5.01  6/2/99    5/1/05
1   5.50  5/2/05    5/7/10
1   5.62  5/5/98    6/1/99
1   4.50  5/8/10    5/10/11
2   3.50  7/2/86    7/3/86
2   3.60  6/1/86    7/1/86
2   3.99  7/4/86    9/9/12
2   3.10  9/10/12   10/6/15
3   2.55  5/5/16    5/6/16
3   2.02  4/5/15    5/4/16
3   1.50  3/2/14    4/4/15

What I need the query to return is

ID  Price PriceDate PriceEndDate SecondPrice SecPriceDate SecPriceEndDate
1   4.50  5/8/10    5/10/11      5.50        5/2/05       5/7/10
2   3.10  9/10/12   10/6/15      3.99        7/4/86       9/9/12
3   2.55  5/5/16    5/6/16       2.02        4/5/15       5/4/16

I've seen questions for SQL Server and MySQL but I'm currently using Oracle SQL. Thank you!!

Upvotes: 0

Views: 129

Answers (1)

MT0
MT0

Reputation: 168041

To get the data as rows:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY id ORDER BY priceEndDate DESC ) AS rn
  FROM   your_table t
)
WHERE  rn <= 2;

Then to PIVOT the rows to columns you can do:

SELECT id,
       "1_PRICE"          AS Price,
       "1_PRICESTARTDATE" AS PriceDate,
       "1_PRICEENDDATE"   AS PriceEndDate,
       "2_PRICE"          AS SecondPrice,
       "2_PRICESTARTDATE" AS SecPriceDate,
       "2_PRICEENDDATE"   AS SecPriceEndDate,
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY id ORDER BY priceEndDate DESC ) AS rn
  FROM   your_table t
)
PIVOT (
  MAX( price ) AS price,
  MAX( priceStartDate ) AS priceStartDate,
  MAX( priceEndDate ) AS priceEndDate
  FOR rn IN ( 1, 2 )
);

Upvotes: 2

Related Questions