Re: SQL Problem - Retrieving first n sorted rows

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.
Received on Sun Oct 25 1998 - 21:53:50 CST