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.

This content is published under the Attribution-Noncommercial-Share Alike 3.0 Unported license.

Technorati Tags: LIMIT, Oracle, ROWNUM, SQL, Tutorial

1. Spool a CSV file from Oracle/ SQL*Plus without quotes
2. Writing basic case switch statements in Oracle PL/SQL
3. Joining columns/ concatenate strings in Oracle PLSQL/ MySQL
4. Changing the HTTP port Oracle uses
5. Oracle: Update From equivalent
6. Setting Database Connections in JBoss/ ATG
7. Oracle: Delete all rows from all tables etc…