Re: SQL Problem - Retrieving first n sorted rows

Hi,

rownums are applied before sorting is done. So the rows with the lowest fifty ronums won't be the rows with the lowest/highest sort criteria.

OraSaurus schrieb:
>
> The proposed solution sounds way too complicated!
> Is DOC_NUMBER unique? If so, whats wrong with just plain old:
>
> select DOC_NUMBER, DOC_TITLE
> from FULL_REC
> where rownum <= 50
> order by DOC_NUMBER;
>
> In article <362F5E95.B6033972_at_uclink4.berkeley.edu>, Jason Jay Weiland
> <archduke_at_uclink4.berkeley.edu> wrote:
> >Ariel,
> >
> > One way to solve this is to join the table to itself and then pull
> >out the ordering.
> >
> >SQL> select * from full_rec order by doc_number;
> >
> > REC_KEY DOC_NUMBER DOC_TITLE
> >--------- ---------- ----------------------------
> > 300 20 Page 1
> > 304 21 Page 2
> > 302 22 Page 1b
> > 303 23 Page 1c
> > 305 25 Page 2a
> > 306 26 Page 2b
> > 307 27 Page 2c
> > 308 28 Page 3
> > 309 29 Page 4
> > 310 30 Page 5
> > 301 31 Page 1a
> >11 rows selected.
> >
> >SQL> edit
> >Wrote file afiedt.buf
> >
> > 1 SELECT full_a.rec_key, full_a.doc_number, full_a.doc_title
> > 2 FROM (SELECT rec_key, doc_number, doc_title
> > 3 FROM full_rec) full_a,
> > 4 (SELECT rec_key, doc_number, doc_title
> > 5 FROM full_rec) full_b
> > 6 WHERE full_a.doc_number >= full_b.doc_number
> > 7 GROUP BY full_a.rec_key, full_a.doc_number, full_a.doc_title
> > 8 HAVING count(*) <= 5
> > 9* ORDER BY count(*)
> >SQL> /
> >
> > REC_KEY DOC_NUMBER DOC_TITLE
> >--------- ---------- --------------------------------------------
> > 300 20 Page 1
> > 304 21 Page 2
> > 302 22 Page 1b
> > 303 23 Page 1c
> > 305 25 Page 2a
> >
> >
> >....if you wanted to order by REC_KEY then simply replace line 6. You
> >can also reverse the order on this one by making line 6 <= rather than
> >>= .
> >
> >Jay!!!
> >
> >
> >Ariel Kirson wrote:
> >
> >> Hello all,
> >>
> >> I have seen a few references to queries which retrieve
> >> the first N rows of a table in various FAQs on the web.
> >> I am however, trying to retrieve the N rows of a table
> >> with the smallest values of a certain column.
> >>
> >> e.g.
> >>
> >> Table : Full_Rec
> >> Columns : Rec_Key (key)
> >> Doc_Number
> >> Doc_Title
> >>
> >> I would like the titles of the 50 lowest Doc_Numbers
> >> (the table is sorted by Rec_Key, and not by Doc_Number
> >> of course).
> >>
> >> I am using Oracle 7.3.3. and PL/SQL 2.3.3.
> >>
> >> Thank you in advance for any solutions,
> >>
> >> Best regards,
> >>
> >> Ariel Kirson
> >> Ex Libris.

--

Regards

Matthias Gresz :-)

Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Mon Oct 26 1998 - 02:06:37 CST