question for select:the PostgreSQL way

xinshi

the mysql way:
select * from tabl_name limit 10,20;

How should i write in PostgreSQL?

is that:
select * from tabl_name set limiter from 10 to 30

is that right?
need help.

smarlowe

select * from table limit x offset y

The SQL92 ansi standard way.

xinshi

thx!

but in mysql,it can't run.
why?
can it run in pgsql?

and also oracle,how to write?

thx again.

[deleted]

MySQL doesn't like standards, that's one of the bad things about MySQL.
Yes it will run in Postgress (why else do you think Scott gave that answer?)

So far the only way of doing it in oracle is with subqueries.
Ask www.google.com and you'll get some examples.

smarlowe

Actually I think you can use the pseudo variable row in oracle like so:

select * from table where row >10 and row <21 or something like that. Klunky, but it works in the versions we have at work. I don't know if that's a DBA addon or what, I'm no Oracle whiz, that's for sure.

xinshi

thank u

toma42

I tried this on our oracle instance but it didn't work:

Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production

SQL> select from claim where row < 10;
select from claim where row < 10
*
ERROR at line 1:
ORA-00936: missing expression

So it could be a DBA addon. If you find out how your db works with this please share!

xinshi

try this
select * from claim where rownum<10;

and
select from claim where rownum<10 minus select from claim where rownum<5;