Re: selecting only 10 rows

On Thu, 7 Jan 1999, Ed Zappulla wrote:

> Does anyone know how to limit the number of rows returned from a select
> statement? I have 5000 rows in a table and just want to grab the first 10
> to look at a sample of them. I thought ROWId would do it but it doesn't
> seem to work. SQLSERVER uses set rowcount = 10.

Hi Ed.

There's been a big discussion on this topic here recently. Look for the subject "tricky sql question"; this about when you need a certain ten rows but if you don't care which 10 rows are returned, it's much easier.

For example:

SELECT col1,col2,col3
FROM some_table
WHERE some_conditions AND

      ROWNUM < 11;

ROWNUM is incremented by 1 from 0 for each row returned by a query, BUT, if you
use an ORDER BY clause, the final order of the rows does not match their rownum.

Hope this helps.

• Richard 'Frog' Barrott --------------------------------|--------------------------------------------- u952297_at_student.canberra.edu.au | QUOTE OF THE WEEK: <insert email address here> | "...capital punishment is our society's | recognition of the sanctity of human life." Ph: Australia 041-2180878 | - Orrin Hatch (US Senator) --------------------------------/