[Oracle] ROW_NUMBER Function

Assume I have a table which name Product, the data looks like:

SELECT ORDER_DATE,
       PRODUCT_ID,
       QTY
FROM PRODUCT
WHERE ORDER_DATE = '20200501'
ORDER BY ORDER_DATE, PRODUCT_ID;

We can make good use of row_number function to retrieve its row number:

SELECT ORDER_DATE,
       PRODUCT_ID,
       QTY,
       row_number() over
           (ORDER BY ORDER_DATE, PRODUCT_ID) AS ROW_NUMBER
FROM PRODUCT
WHERE ORDER_DATE = '20200501'
ORDER BY ORDER_DATE, PRODUCT_ID;

If we would like get the second record, the SQL statement looks like:

SELECT ORDER_DATE,
       PRODUCT_ID,
       QTY,
       ROW_NUMBER
FROM (
         SELECT ORDER_DATE,
                PRODUCT_ID,
                QTY,
                row_number() over
                    (ORDER BY ORDER_DATE, PRODUCT_ID) AS ROW_NUMBER
         FROM PRODUCT
         WHERE ORDER_DATE = '20200501'
         ORDER BY ORDER_DATE, PRODUCT_ID
     )
WHERE ROW_NUMBER = 2;

Reference

[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm