Find n lowest values from table
                                                 Last update (2008-02-23 13:47:56)
                                                                                                                   Date added (2008-02-23 13:29:16)

Summary
We will use the example of RANDOM_VALUES table. You can find it here
Create a table with random values.

To select the n lowest values exexute:
SELECT * FROM 
(SELECT * FROM RANDOM_VALUES 
ORDER BY col1 ASC) WHERE ROWNUM < :n+1;
Looking at the explain plan it costs only one full table scan to RANDOM_VALUES and a SORT ORDER BY STOPKEY.

Another way to get the same result.
SELECT a.ID, a.col1 FROM RANDOM_VALUES a
WHERE :n>(SELECT COUNT(*) FROM RANDOM_VALUES 
WHERE a.col1>RANDOM_VALUES.col1);
But it has more cost because of the 2 full table scans at RANDOM_VALUES table
Reviews
Filters
Search