OracleConnections

Oracle Community Network

Solution Compatible Oracle and MySQL for LIMIT clause

Is there a solution that is compatible Oracle and MySql to limit the number of rows returned by a query ?

For example in mysql there is the LIMT clause

SELECT * FROM myTable LIMT 10;

In Oracle we use a condition on the rownum column

I want some code that works for both MySQL and Oracle

0 members like this

Permalink Reply by Barn Mitchel on August 22, 2013 at 3:37pm

If your table has a unique id column (or combination of columns), you can do the following:

select t.* from t where (select count(*) from t t2 where t2.id <= t.id) <= 10;

The correlated subquery in the where clause is standard SQL syntax so it should run in any database.

The performance should be ok on small tables. It would be improved with an index on t(id).

Permalink Reply by Elizabeth Warren on August 22, 2013 at 3:39pm

in oracle it's

select * from mytable where rownum /span> 11

the alternative is an analytic function but mysql does not support that.

Permalink Reply by Mike Trout on August 22, 2013 at 3:43pm

To get the last 5 records

SELECT * FROM account HAVING (SELECT MAX(id) FROM account) - 5 /span> id

To get the first 5 records

SELECT * FROM account HAVING (SELECT MIN(id) FROM account) + 5 > id

Welcome to
OracleConnections

Sign Up
or Sign In

Or sign in with: