how do i limit the number of results returned in a select query?

[carpeliam]

quite possibly a simple question, but i can't seem to find the answer.

I'd like to say "give me the first 50 results of query x". MySQL does it like this:

SELECT * FROM table LIMIT 5,10; #this will retrieve rows 6 through 15

I'm hoping for something that will actually limit the number of rows I ask for, not simply ask for everything and then limit the number of rows I display.

Liam Morley
lmorley@wpi.edu
"light the deep, and bring silence to the world.
light the world, and bring depth to the silence."

 

[SantaMufasa]

Liam,

In Oracle, to obtain just the first 50 rows of a query, we use the following code:

SELECT <expression(s)> FROM <table_name>
WHERE ROWNUM <= 50;

If you wish to read the entire table (for example to find the top 50 rows of an ORDER BY, you would say:

SELECT <expression(s)>
FROM (SELECT <expressions> FROM <table_name> ORDER BY <expression(s)>)
WHERE ROWNUM <= 50;

Let us know if this resolves your need.,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:41 (21Jul04) UTC (aka "GMT" and "Zulu"), 12:41 (21Jul04) Mountain Time)

 

[carpeliam]

that works, thanks <p>Liam Morley<br><A HREF="mailto:"></A><br>&quot;light the deep, and bring silence to the world.<br>light the world, and bring depth to the silence.&quot;

 

[Glowworm27]

Hey Santa,

If I only want the very first row would I use
Where Rownum=1 or Rownum=0?

I guess what I am asking is rownum zero based?

Thanks


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

I got it, Rownum is 1 based, So if I need to return the first row of my query the Where Clause would be....


Where ROWNUM=1

Here is the BOL article

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements6a.htm#33158

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips