SQL Oracle Specify start point using ROW_NUMBER()

Question

I got this code:

ROW_NUMBER() OVER (partition by ID ORDER BY ID DESC, year DESC, month DESC, day DESC) rank

I would like to fix the exactly year, month and day to start to rank. Where should i put this condition in that situacion?

Thank you in advance!

-----------------------------------------------------EDIT:----------------------------------------------------

I'm going to detail better my situtation in order to show you what I exactly want.

Let's use this sample table and assume there are values for every date.

| Year | Month | Day | ID        | Rank       |
|------|-------|-----|-----------|------------|
| 2017 | 9     | 14  | 9555      | 1          |
| 2017 | 9     | 13  | 9555      | 2          |
| 2017 | 9     | 12  | 9555      | 3          |
| 2017 | 9     | 11  | 9555      | 4          |
| 2017 | 9     | 14  | 3000      | 1          |
| 2017 | 9     | 13  | 3000      | 2          |
| 2017 | 9     | 12  | 3000      | 3          |
| 2017 | 9     | 11  | 3000      | 4          |

This table is what you can get using the following code:

Select *
From (Select Year,
             Month,
             Day,
             ID,
             ROW_NUMBER() OVER (partition by ID ORDER BY ID DESC, Year DESC, Month DESC, Day DESC) rank
      From table1
      Where (Condition)
      )
Where Rank < 5;   

Our sample table is conditioned by the fact that the most recent row is from 14/09/2017. My question is how could I get the last 4 rows from 13/09/2017?

Answer 1

Use a CASE statement:

CASE
  WHEN TO_DATE(
         TO_CHAR( year, 'FM0000' )
         || '-' || TO_CHAR( month, 'FM00' )
         || '-' || TO_CHAR( day, 'FM00' ),
         'YYYY-MM-DD'
       ) > DATE '2017-01-01' -- Your start date
  THEN ROW_NUMBER() OVER (partition by ID
                          ORDER BY ID DESC, year DESC, month DESC, day DESC)
END AS rank

But why are you storing a date in year, month and day columns rather than just one DATE data-type?

Update:

how could I get the last 4 rows from 13/09/2017

This will get apply your rank only to rows on or before 2017-09-13:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (partition by ID 
                            ORDER BY ID DESC, Year DESC, Month DESC, Day DESC) rank
  FROM   table1 t
  WHERE  TO_DATE(
           TO_CHAR( year, 'FM0000' )
           || '-' || TO_CHAR( month, 'FM00' )
           || '-' || TO_CHAR( day, 'FM00' ),
           'YYYY-MM-DD'
         ) <= DATE '2017-09-13'
)
WHERE rank < 5