Fun* with Oracle’s ROW_NUMBER()

(*Fun in a very loose tense of the word)

ROW_NUMBER returns a unique row number for each row of a query. It can be combined with an PARTITION BY and ORDER BY clause, so that you have control of the results.

For example, using the order by clause within a subquery we can return specific row numbers. Something we can’t do with the traditional ROWNUM value.

WITH myView AS
( SELECT myColumn,
ROW_NUMBER() OVER ( ORDER BY myColumn ) rn
FROM myTable )
SELECT myColumn
FROM myView mv
WHERE mv.rn BETWEEN 5 AND 10;

This query would have returned 5 rows much more effctly than using ROWNUM.

We can link ROW_NUMBER with SYS_CONNECT_BY_PATH to return row data in a single column.

WITH myView AS
( SELECT myColumn,
ROW_NUMBER() OVER ( ORDER BY myColumn ) rn
FROM myTable )
SELECT MAX(SUBSTR(SYS_CONNECT_BY_PATH(myColumn,','),2)) myRowData
FROM myView
START WITH rn = 1
CONNECT BY rn = PRIOR rn+1;

See here to see how adding the PARTITION BY clause gives further options if you want to convert row data into a column strings grouped by further data criteria.

Done.