set number of rows to be selected 1

[anirudhadeo]

Hi
Is there any way in oracle by which we can set the max number of rows to be selected.example : first 100 rows etc

waiting for your reply
Regards Anirudha

 

[CRoberts]

Yes. Use the rownum pseudocolumn in the where clause.

i.e. WHERE ROWNUM <= 100

You can not sort directly, however. If you want a sorted list of the first 100 items, then you use an inline query:

SELECT EName
FROM (SELECT EName FROM Emp ORDER BY EName)
WHERE ROWNUM <= 100

 

[Hehenka]

I don't know about setting such parameter.But you can use this:

Select * from my_table where rownum < 101;

It returns first 100 rows.

 

[anirudhadeo]

HI
Thanx CRoberts, Hehenka.
Regards anirudha

 

[Turkbear]

Just a mild warning:

Be careful with vocabulary..There are no first ( or last )
records in an Oracle table ( in the sense that, say , SqlServer or Access may define it)..

The query from Hehenka will return 100 rows but not 'the first' 100 - If you mean the first 100 of a sorted subset
then use CRoberts second example..It is the preferred method of getting a limited number of sorted values from an Oracle table..