Tuesday, March 2, 2010

Mimic mySQL's LIMIT command in Oracle

I have struggled in the past and have heard of people struggling with writing a SQL statement in Oracle that would mimic mySQL's command LIMIT.

LIMIT doesn't exist in Oracle and there are two ways of displaying the range of records you want from a table. The first one is to use PLSQL. I'm not going to get into details here but all you have to do is do a SELECT statement INTO a CURSOR datatype, iterate your cursor and exit when your loop counter hits the number of rows you want.

As you can see this solution is not very effective and the best way of doing this is by creating a simple statement using the rownum column.

For instance, if you wanted to select the 10 first items from the item table you would do the following:

SELECT * FROM item WHERE rownnum < 11;

This should return the first 10 rows.