martedì 4 maggio 2010

Limiting the result sets in Oracle

In Oracle DB the number of rows can be limited using the ROW_NUMBER() function:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY <ordering_field> DESC) AS ROWNUMBER, <other columns>
FROM <table_name>
[WHERE <conditions>]
)
WHERE ROWNUMBER <= <max_row_number>

Example, retrieve the id of the last modified curve among curves with type equals to "ATT_MENS":

SELECT id_curve FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY sys_modifydate DESC) AS ROWNUMBER, id_curve
FROM curve
where curve_type = 'ATT_MENS'
)
WHERE ROWNUMBER <= 1;