CODEWITHSUNDEEP
sqloracle-databaseoracle11g
jagamot
jagamot

Reputation: 5464

SQL - Compare Data from the same table

I have the below table with following columns -

ID-1, ID-2 and ID-3 - Key columns

Rate - Shows the rate of a product based on the above IDs

Date - implied the date for which that Rate applies.

+-------+----------+-------------+----------+------------+
| ID-1  | ID-2     | ID-3        | Rate     |   Date     |
+-------+----------+-------------+----------+------------+
| 2000  | 1        | 100         | 50       | 12/30/2013 |
+-------+----------+-------------+----------+------------+
| 2000  | 1        | 100         | 75       | 10/11/2013 |
+-------+----------+-------------+----------+------------+
| 2000  | 1        | 100         | 100      | 12/15/2013 |
+-------+----------+-------------+----------+------------+
| 2000  | 2        | 100         | 50       | 10/30/2013 |
+-------+----------+-------------+----------+------------+
| 2000  | 2        | 100         | 75       | 10/11/2013 |
+-------+----------+-------------+----------+------------+
| 2000  | 2        | 100         | 100      | 09/15/2013 |
+-------+----------+-------------+----------+------------+
| 3000  | 2        | 200         | 25       | 1/1/2014   |
+-------+----------+-------------+----------+------------+
| 4000  | 2        | 100         | 100      | 12/1/2013  |
+-------+----------+-------------+----------+------------+
| 4000  | 1        | 200         | 75       | 1/1/2014   |
+-------+----------+-------------+----------+------------+
| 4000  | 2        | 100         | 25       | 11/1/2014  |
+-------+----------+-------------+----------+------------+

For each combination of ID-1, ID-2 and ID-3 I want to ouput 2 recent most rates in the following format - Previous Rate - is the rate for the second most recent date Current Rate - is the rate for the recent most date

+-------+----------+-------------+---------------+----------------+
| ID-1  | ID-2     | ID-3        | Previous Rate |  Current Rate  |
+-------+----------+-------------+---------------+----------------+
| 2000  | 1        | 100         | 100           | 50             |
+-------+----------+-------------+---------------+----------------+
| 2000  | 2        | 100         | 75            | 50             |
+-------+----------+-------------+---------------+----------------+
| 3000  | 2        | 200         |               | 25             |
+-------+----------+-------------+---------------+----------------+
| 4000  | 1        | 200         |               | 75             |
+-------+----------+-------------+---------------+----------------+
| 4000  | 2        | 200         | 25            | 100            |
+-------+----------+-------------+---------------+----------------+

Any ideas?

Upvotes: 0

Views: 738

Answers (3)

Dba
Dba

Reputation: 6649

This might help you.

SELECT id1, id2, id3, prev_rate, curr_rate
FROM(
     SELECT id1, 
            id2, 
            id3,
            rate AS curr_rate, 
            LEAD(rate,1) OVER (PARTITION BY id1, id2, id3 ORDER BY date DESC) AS prev_rate,
            DENSE_RANK() OVER (PARTITION BY id1, id2, id3 ORDER BY date DESC) AS rnk
     FROM   <table_name>
     )
WHERE rnk = 1;

Upvotes: 0

MT0
MT0

Reputation: 168671

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test ( "ID-1", "ID-2", "ID-3", Rate, "Date" ) AS
          SELECT 2000  , 1        , 100         , 50       , TO_DATE( '12/30/2013', 'MM/DD/YYYY' ) FROM DUAL
UNION ALL SELECT 2000  , 1        , 100         , 75       , TO_DATE( '10/11/2013', 'MM/DD/YYYY' ) FROM DUAL
UNION ALL SELECT 2000  , 1        , 100         , 100      , TO_DATE( '12/15/2013', 'MM/DD/YYYY' ) FROM DUAL
UNION ALL SELECT 2000  , 2        , 100         , 50       , TO_DATE( '10/30/2013', 'MM/DD/YYYY' ) FROM DUAL
UNION ALL SELECT 2000  , 2        , 100         , 75       , TO_DATE( '10/11/2013', 'MM/DD/YYYY' ) FROM DUAL
UNION ALL SELECT 2000  , 2        , 100         , 100      , TO_DATE( '09/15/2013', 'MM/DD/YYYY' ) FROM DUAL
UNION ALL SELECT 3000  , 2        , 200         , 25       , TO_DATE( '1/1/2014', 'MM/DD/YYYY' )   FROM DUAL
UNION ALL SELECT 4000  , 2        , 100         , 100      , TO_DATE( '12/1/2013', 'MM/DD/YYYY' )  FROM DUAL
UNION ALL SELECT 4000  , 1        , 200         , 75       , TO_DATE( '1/1/2014', 'MM/DD/YYYY' )   FROM DUAL
UNION ALL SELECT 4000  , 2        , 100         , 25       , TO_DATE( '11/1/2014', 'MM/DD/YYYY' )  FROM DUAL;

Query 1:

WITH rankings AS (
  SELECT "ID-1",
         "ID-2",
         "ID-3",
         Rate,
         DENSE_RANK() OVER (PARTITION BY "ID-1", "ID-2", "ID-3"
                            ORDER BY "Date" DESC, Rate DESC ) AS "Rank"
  FROM   test
)
SELECT "ID-1",
       "ID-2",
       "ID-3",
       MAX( CASE "Rank" WHEN 1 THEN Rate END ) AS current_rate,
       MAX( CASE "Rank" WHEN 2 THEN Rate END ) AS previous_rate
FROM   rankings
GROUP BY
       "ID-1",
       "ID-2",
       "ID-3"

Results:

| ID-1 | ID-2 | ID-3 | CURRENT_RATE | PREVIOUS_RATE |
|------|------|------|--------------|---------------|
| 2000 |    1 |  100 |           50 |           100 |
| 2000 |    2 |  100 |           50 |            75 |
| 3000 |    2 |  200 |           25 |        (null) |
| 4000 |    1 |  200 |           75 |        (null) |
| 4000 |    2 |  100 |           25 |           100 |

Upvotes: 2

Lajos Veres
Lajos Veres

Reputation: 13725

Maybe something like this?

select
  id-1,
  id-2,
  id-3,
  first_value(rate) over (partition by id-1, id-2, id-3 order by date desc) as current_rate
  lead(rate,1) over (partition by id-1, id-2, id-3 order by date desc) as prev_rate
from
  table

Upvotes: 2

Related Questions