Top-N-SQL using RANK() and DENSE_RANK()DENSE_RANK()

RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5.

DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

- using rank()

SELECT segment_name, bytes

  FROM ( SELECT segment_name, bytes, RANK() OVER (ORDER BY bytes DESC) sal_rank

           FROM user_segments )

 WHERE sal_rank <= 5;

 -- using dense_rank()

 SELECT segment_name, bytes

  FROM ( SELECT segment_name, bytes, DENSE_RANK() OVER (ORDER BY bytes DESC) sal_rank

           FROM user_segments )

 WHERE sal_rank <= 5;