Re: Finding the 10 smallest values in a column

I am sorry, I wrote "will return 20 rows" when it should be "will return 12 rows".

Solomon Yakobson.

In article <879630927.29097_at_dejanews.com>,   syakobson_at_erols.com wrote:
>
> 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

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