Re: Finding the 10 smallest values in a column

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/11/15
Message-ID: <19971115013500.UAA13642@ladder02.news.aol.com>#1/1

This is for everyone who wants to select the first n rows.

It should by now be quite well established that using ROWNUM does not work. If you
are still not convinced then read the
section ROWNUM in the SQL*PLUS manual or on line help.

The stored function below finds the value for the nth row of the column you are ordering by.

DO NOT use it for tables with millions of rows, but I have checked it against 20,000 rows and found performance acceptable. Worst case was 7 seconds to begin the fetch of 19,999 rows.

Indexing of the ORDER BY column is essential.

This example is based on a table ORGANIZATION with a column NAME VARCHAR2(30) (among others).

Create or replace
FUNCTION Organization_Find_Row(p_Row_Number NUMBER) RETURN VARCHAR2
IS
  CURSOR Organization_Cur IS
   SELECT name
     FROM organization
   ORDER BY name;
t_Name organization.name%TYPE;
BEGIN
   OPEN Organization_Cur;
   FOR loop_count IN 1 .. p_Row_Number LOOP     FETCH Organization_Cur
     INTO t_Name;
   END LOOP;
   CLOSE Organization_Cur;
   RETURN t_Name;
END; How to use the function

SQL> SELECT name
2 FROM organization
3 WHERE name <= organization_find_row(50) 4 AND name >= organization_find_row(1) 5* ORDER BY; Note line 4 which at appears redundant. Performance without it is slower by a factor of 10. You need to think about sets to see why.

You could also use

SQL> SELECT name
2 FROM organization
3 WHERE name <= organization_find_row(100) 4 AND name >= organization_find_row(51) 5* ORDER BY; to find the next group of 50 records.

Enjoy!
John

John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd LVO-UPL
Las Vegas NV 89103

FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com

e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/

The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.

Received on Sat Nov 15 1997 - 00:00:00 CST