Re: SQL Problem - Retrieving first n sorted rows

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Thu, 22 Oct 1998 09:34:29 -0700
Message-ID: <362F5E95.B6033972@uclink4.berkeley.edu>

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 Thu Oct 22 1998 - 11:34:29 CDT