Re: How to limit the number of rows returned in a select statement

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/06/04
Message-ID: <33954712.17803310@www.sigov.si>#1/1

On Tue, 03 Jun 1997 17:04:05 +0200, Stefan Fallet <stfallet_at_fallet.com> wrote:

>Hi does anyone know how to limit he number of rows returned by a select.
>I tryed using rownum. but this does not work with an order by.
>
>my select: select X,Y from table_a order by Y.
>
>I only want the first 15 rows after the sort.
>Using rownum <=15 brings back the first 15 rows in the table.
>
>Need help fast
>
>Thanks
>
>Stefan Fallet
>
>E-Mail: stfallet_at_fallet.com

Using ORDER BY and WHERE ROWNUM <= n in the same SELECT doesn't work because WHERE constraint is applied before ORDER BY. That way querry returns first n rows it finds from table and only then sorts this n rows in spcified order.

As one of possible solutions of your first_15 problem using yust SQL you could use:

SELECT x, y FROM table_a a
WHERE 15 >= (SELECT COUNT(y) FROM table_a b

             WHERE b.y <= a.y)
  AND a.y IS NOT NULL
ORDER BY a.y;

Note however that if there is more then 1 record whith same y value on 15th position none of this records will be returned, thus query will return less then 15 records.

Regards,

---

Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3

e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000

---

Received on Wed Jun 04 1997 - 00:00:00 CDT