CREATE TABLE PRICES ( NAME, VALIDFROM, PRICE ) AS
SELECT 'GOLD', DATE '2017-01-01', 36000 FROM DUAL UNION ALL
SELECT 'GOLD', DATE '2017-09-25', 42000 FROM DUAL UNION ALL
SELECT 'GOLD', DATE '2017-10-03', 40800 FROM DUAL UNION ALL
SELECT 'GOLD', DATE '2017-12-12', 39500 FROM DUAL UNION ALL
SELECT 'GOLD', DATE '2019-09-03', 49700 FROM DUAL UNION ALL
SELECT 'SILVER', DATE '2017-01-21', 28000 FROM DUAL UNION ALL
SELECT 'SILVER', DATE '2018-01-23', 23000 FROM DUAL UNION ALL
SELECT 'SILVER', DATE '2018-02-26', 17000 FROM DUALTable created.
SELECT * FROM
(
SELECT
NAME
,VALIDFROM
,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
FROM PRICES
WHERE VALIDFROM <=DATE '2017-10-25'
)
WHERE RNK=1 | NAME | VALIDFROM | RNK | GOLD | 03-OCT-17 | 1 | SILVER | 21-JAN-17 | 1 |
|---|
WITH CTE_RNK (NAME, VALIDFROM, RNK) AS (
SELECT
NAME
,VALIDFROM
,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
FROM PRICES
)
SELECT
*
FROM CTE_RNK
WHERE
VALIDFROM <=DATE '2017-10-25'
AND RNK=1 no data found
WITH CTE_RNK (NAME, VALIDFROM, RNK) AS (
SELECT
NAME
,VALIDFROM
,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
FROM PRICES
)
SELECT
*
FROM CTE_RNK
WHERE
VALIDFROM <=DATE '2017-10-25'
AND RNK=3 | NAME | VALIDFROM | RNK | GOLD | 03-OCT-17 | 3 | SILVER | 21-JAN-17 | 3 |
|---|
CREATE VIEW PRICES_AT_GIVEN_DATE (NAME, VALIDFROM, RNK) AS
(SELECT
NAME
,VALIDFROM
,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
FROM PRICES) View created.
SELECT
*
FROM PRICES_AT_GIVEN_DATE
WHERE
VALIDFROM <=DATE '2017-10-25'
AND RNK=1 no data found
SELECT
*
FROM PRICES_AT_GIVEN_DATE
WHERE
VALIDFROM <=DATE '2017-10-25'
AND RNK=3 | NAME | VALIDFROM | RNK | GOLD | 03-OCT-17 | 3 | SILVER | 21-JAN-17 | 3 |
|---|