LIMIT equivalent for Oracle

So you use MySQL etc… and you’re used to adding a LIMIT clause to your query for example,

SELECT * FROM table WHERE name = ‘bob’ LIMIT 1

And now you’re using Oracle and want to do the same thing? But it throws some dread ORA error – why?

Well because Oracle doesn’t have LIMIT, thats why!

So instead we have ROWNUM and using this instead we can do pretty much the same thing by adding an AND to the where clause as thus:

SELECT * FROM table WHERE name = ‘bob’ AND rownum = 1

This also means we can do < 10, > 10 etc…

And thats what I learnt in Oracle land today.

One Comment

  • i tried this query and it didnt work. i think we have to use rownum<=2

Join the Discussion

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>