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

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

Post by Stefan Falle » Wed, 04 Jun 1997 04:00:00



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


 
 
 

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

Post by Scott Maxso » Wed, 04 Jun 1997 04:00:00



> 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



Stefan,

Sorry, I don't know a way to make this work with any arbitrary ORDER BY
clause in your SELECT - as I understand it, ROWNUM's get assigned before
the sort (which you've discovered). However, if your table has an index
on column Y, and this index is used for retrieval (you could use an
optimizer hint to ensure this) then records are fetched in order based
on this index; you should be able to eliminate the ORDER BY clause from
your statement. There are a few pitfalls; see Gurry and Corrigan,
"Oracle Performance Tuning", pp 199-201 for more on this. Probably
someone else here knows more than I do about the subject, too ...?

Otherwise I think you'll have to use something other than pure SQL, like
PL/SQL or Pro*C, to get what you want.

Hope this helps,

Scott Maxson


 
 
 

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

Post by jguer.. » Thu, 05 Jun 1997 04:00:00


On Tue, 03 Jun 1997 17:04:05 +0200, Stefan Fallet
<snip>

Quote:>I tryed using rownum. but this does not work with an order by.

<snip>

It's not a pretty solution but it works:  use pl/sql and retrieve
the first 15 rows.

Rownum works before the order by so you get the first 15 rows
and then they are sorted.
HTH

--

Oracle DBA Consultant

 
 
 

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

Post by Jurij Mod » Thu, 05 Jun 1997 04:00:00


On Tue, 03 Jun 1997 17:04:05 +0200, Stefan Fallet


>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



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

============================================================

 
 
 

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

Post by Paul Sandwel » Thu, 05 Jun 1997 04:00:00



> 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



Depending on the reason you want to limit the number of rows returned,
you can fudge it.

If you want to see only a few rows because that gives whatever info you
need and the query execution itself is not a matter of concern, then use
your original SELECT statement as a subselect of an outer SELECT which
limits on ROWNUM. Like this:

SELECT * from (select X,Y from table_a order by Y) where rownum < 10;

If, on the other hand, you want to limit the number of rows because the
query execution is a problem, then I'm afraid you're stuck.

Good luck!

Paul

 
 
 

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

Post by Stephen A. Rodger » Fri, 06 Jun 1997 04:00:00


The easiest way I've seen to do this in the past is to create a view
which has your select statement and where clause, and include your count
of records based on rownum.

Next, simply do a select from the view and add your ORDER BY clause to
that.  The order by happens after rows are returned from the view, so it
will not harm the sequence of the rownumbers being returned.  

Good luck!
--
Stephen A. Rodgers
Prominence Consulting, Inc.

http://www.icca.org/firms/f15498.htm

 
 
 

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

Post by Allen Kirb » Fri, 06 Jun 1997 04:00:00


<snip>

Quote:> If you want to see only a few rows because that gives whatever info you
> need and the query execution itself is not a matter of concern, then use
> your original SELECT statement as a subselect of an outer SELECT which
> limits on ROWNUM. Like this:

> SELECT * from (select X,Y from table_a order by Y) where rownum < 10;

<snip>

Paul,
I don't THINK you can use an order by clause in a subselect, so the
above won't work.  I could be wrong, however.
---
Allen Kirby                     AT&T ITS Production Services

 
 
 

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

Post by Paul Sandwel » Fri, 06 Jun 1997 04:00:00



> <snip>
>> If you want to see only a few rows because that gives whatever info you
>> need and the query execution itself is not a matter of concern, then use
>> your original SELECT statement as a subselect of an outer SELECT which
>> limits on ROWNUM. Like this:

>> SELECT * from (select X,Y from table_a order by Y) where rownum < 10;

> <snip>

> Paul,
> I don't THINK you can use an order by clause in a subselect, so the
> above won't work.  I could be wrong, however.
> ---

Thanks for the check there, Alan, you are indeed correct. But you can
use a GROUP BY function in the subselect which, in simple cases such as
selecting a single column from one table, has the same effect. I'll
check it out next time before shooting off my mouth!

Paul

 
 
 

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

Post by Jurij Mod » Fri, 06 Jun 1997 04:00:00


On Wed, 04 Jun 1997 11:39:45 -0500, Paul Sandwell


>Depending on the reason you want to limit the number of rows returned,
>you can fudge it.

>If you want to see only a few rows because that gives whatever info you
>need and the query execution itself is not a matter of concern, then use
>your original SELECT statement as a subselect of an outer SELECT which
>limits on ROWNUM. Like this:

>SELECT * from (select X,Y from table_a order by Y) where rownum < 10;

I am affraid this won't work, because you can not use ORDER BY in a
subquery!

Regards,

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

============================================================

 
 
 

1. Returning a limited number of rows from a SELECT statement

This is probably a stupid questions, but I can't seem to figure it out...

I'm running SQL 6.5 on NT 4.0 SP2. The primary client software is a Delphi
2.0 application that uses ODBC (via Borland's BDE layer) to access the SQL
databases. Everything works just fine.

The question I have is this: Is there any way to cause the SQL server to
limit the number of rows it returns in response to a SELECT statement? For
example, if the results of a SELECT would normally return 500 rows, is
there a way to only return the first 10 rows? I have seen other software
(like Cold Fusion, if you've ever heard of that) that provides a way at
their API level to set a "MAXROWS" count. This works fine (in Cold Fusion),
but I have been unable to find a way to do this under Delphi 2.0/BDE/ODBC.

Also, the MAXROWS parameters brings up another question: Is the
functionality of limiting the number of returned rows handled at the server
or in the local software? If it's in the local software (either within Cold
Fusion or in ODBC), then does that mean that the SQL server must still
process all 500 rows (from my previous example), and the client software
just stops requesting more data after the first "MAXROWS" rows? If this is
the case, it seems very inefficient of server resources.

Thanks in advance for any ideas!

Regards,
Eric Lenington

2. can't update record

3. How do you Limit the number of rows returned in a SELECT statement ??

4. Oracle Timeline, Year 2002

5. How to limit the number of rows returned by SELECT

6. US-GA-MANUFACTURING/INVENTORY CONSULTANTS

7. Limit the Number of Rows Returned by Select ?

8. How to update table on a remote server from NEWERA

9. Limiting the returning rows number of a SELECT

10. limit number of rows returned with select

11. Limit number of rows returned by SELECT

12. Limit Number of Recordset Return from SELECT statement

13. Limiting number of records returned by the SELECT statement