Re: selecting only 10 rows

select * from table where somecolumn like 'THIS%' AND rownum < 11;

Bear in mind that you will get back the first 10 rows, but they will not be ordered. If you add an order by clause, the sort will take place after the rows are grabbed, so this is not workable for stuff like name searches, where you might just want the first ten rows that start with 'SMITH', but do want the first row values.

So... to sort the data first, create a view that looks like:

CREATE VIEW FRED AS SELECT NAME, CUST_ID FROM SOMETABLE WHERE NAME LIKE 'SMITH%'; Then, select name, value from fred where rownum < 11;

You still have to plow through all of the SMITH% rows, but you don't pump them through the net back to the client. The server works like hell, but at least you save a bunch of packet fetches.

Oh, I just slapped this syntax off the top of my head, so get the syntax book out and verify this. Of course, this example is trivial, but illustrates an important principle.

Roger Snowden
Sr. Systems Engineering Specialist
Oracle Corporation
rsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious

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.
Received on Mon Jan 11 1999 - 21:57:33 CST