Re: Finding the 10 smallest values in a column

Nice try but... Your solution will work only if column name can not have duplicates. Assume you have 20 rows in table ORGANIZATION and column name has values 'org1' through 'org5' each repeated 4 times. Then your query to find 10 smallest values in column name:

 SQL> SELECT name
   2 FROM organization
   3 WHERE name <= organization_find_row(10)    4 AND name >= organization_find_row(1)    5* ORDER nameBY;

will return 20 rows.

I am not aware of any SQL solution when duplicates are allowed and shoudl be treated as seperate occurrences. If no duplicates are allowed or you want to treat them as one value SQL solution exists and is much simler:

SELECT DISTINCT a.col1 FROM tbl1 a WHERE N < (SELECT COUNT(DISTINCT

b.col1) -- Where N is number of rows you want. FROM tbl1 b WHERE a.col1 >
b.col1 -- Change > to < if you are looking for highest values. ) ORDER BY
a.col1; -- Add DESC if you are looking for highest values.

But even this SQL solution cant offer performance of PL/SQL:

DECLARE CURSOR c1 IS SELECT col1 -- SELECT DISTINCT col1 if you are looking for distinct values. FROM tbl1 ORDER BY col1; -- Add DESC if you are looking for highest values. BEGIN FOR c1rec IN c1 LOOP . . EXIT WHEN c1%ROWCOUNT = N; -- Where N is number of rows you want. END LOOP; END;

Solomon Yakobson.

In article <19971115013500.UAA13642_at_ladder02.news.aol.com>,   jomarlen_at_aol.com (Jomarlen) wrote:
>
> Hi
>
> 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.
> -------------------------------------------------------

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet