Sometimes you wish to select the top or highest 'n' rows of a sorted data-set.
(or the bottom 'n' rows -- Reversing the sort will switch from top to bottom.)
Simialarly, you may wish to only return the 'nth' row. For example, from a data set sorted by date, you may wish to find the
oldest 10 records, or the one 3rd youngest record. In my experience there are 3 main ways to do this:
1) Use the psudo-column ROWNUM.
2) Use the Analytic functions Rank(), Dense_Rank(), or Row_Number().
3) Use the Fetch and Offset keywords.
For the following examples, I'll use the a 'WITH' data set. Pre-pend it to all the other examples that follow to run them.
with
w_data ( id, nm ) as
( select 30, 'Ted' from dual union all
select 68, 'Dan' from dual union all
select 21, 'Joe' from dual union all
select 49, 'Ben' from dual union all
select 12, 'Sam' from dual union all
select 57, 'Art' from dual )
select id, nm
from w_data
order by nm ;
---
{with...}
select id, nm
from w_data
order by id ;
The ROWNUM Psudo-Colum
The 'rownum' psudo column can be confusing; it returns a sequential number for each row as it is pulled from the source.
It doesn't conform to any sort unless you build that into your query. Notice the example below; the second query returns nothing.
It's trying to limit the pull to the 4th row -- but each row that's tentatively pulled and tested against the 'WHERE' clause,
is assigned to be the first row, so it fails to qualify.
{with...}
select id, nm /* Returns 3 rows */
from w_data
where rownum < 4 ;
{with...}
select id, nm /* Returns no rows */
from w_data
where rownum = 4 ;
However, if you use 'ROWNUM' properly, it can find your requested rows.
Use a double sub-query as shown below, you can assign the 'ROWNUM' as a ranking
after sorting the data (in this example sorting by "nm").
you can then use the 'WHERE' clause to select out the 'rankings' for which
you are looking: top-3 (where rnk < 4), 3rd row (where rnk = 3).
And, notice that if you want to select the Bottom(n) rows, you just need to change the initial sort to "nm desc"
{with...}
select id, nm, rnk
from ( select id, nm
, rownum as rnk
from ( select id, nm from w_data order by nm )
)
where rnk < 4
;
/* Try other 'WHERE' clauses: */
a) where rnk between 2 and 4
b) where rnk = 2
c) where rnk >= 3
Analytic Ranking
Using the analytic functions Rank(), Dense_Rank(), or Row_Number -- you can 'rank' the rows
and select out the nth row or the top(n) rows as shown in this example.
Here I use the Row_Number() function, it does differ from the two 'rank' functions, but I'll
leave that to another post focusing on those differences. Make sure you specify the correct
sort order in the "(order by ---)" part of the analytic function.
{with...}
select id, nm, rnk
from ( select id, nm
, row_number() over (order by nm) as rnk
from w_data
)
where 1=1
and rnk <= 4 /* top 4 records */
-- and rnk in (2,4,6) /* rank is 2nd, 4th, or 6th */
-- and rnk = 1 /* first record */
;
Offset and Fetch
The "OFFSET" & "FETCH" offer a very similar functionality -- for more details,
see my other post on these keywords.
Put these after the sort (order by __ ).
The "OFFSET" specifies how many rows to skip, and the "FETCH" specifies how many rows to return.
Examples:
a) offset 0 rows fetch next 10 rows only -- get the top 10 rows
b) offset 2 rows fetch next 1 row only -- get the 3rd row
{with...}
select id, nm
from w_data
order by nm
OFFSET 2 rows
FETCH next 3 rows only
;
