LIMIT modifier

[Roderick A. Anderson]

I'm dealing with a project that requires me to query a MS SQL Server. Not
a PostgreSQL issue I know but I want to ask if the LIMIT modifier is SQL
standard? MS SQL Server uses a TOP modifier.

select top 1 * from xxx;

Whereas all the SQL RDBMS' I've used before used

select * from xxx limit 1;

Any enlightenment is appreciated. (I'm putting notes in the code to
reflect the non-standard stuff I've had to deal with.)
TIA,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Arjen van der Meijden]

Rod,

Have a look at the bottom of this page:
http://www.postgresql.org/docs/curre...ql-select.html

"Nonstandard Clauses

The clauses DISTINCT ON, LIMIT, and OFFSET are not defined in the SQL
standard."

TOP x isn't in the standard as well, afaik.

Best regards,

Arjen

Roderick A. Anderson wrote:

I'm dealing with a project that requires me to query a MS SQL Server. Not
a PostgreSQL issue I know but I want to ask if the LIMIT modifier is SQL
standard? MS SQL Server uses a TOP modifier.

select top 1 * from xxx;

Whereas all the SQL RDBMS' I've used before used

select * from xxx limit 1;

Any enlightenment is appreciated. (I'm putting notes in the code to
reflect the non-standard stuff I've had to deal with.)
TIA,
Rod

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Roderick A. Anderson]

On Mon, 15 Dec 2003, Arjen van der Meijden wrote:

"Nonstandard Clauses

The clauses DISTINCT ON, LIMIT, and OFFSET are not defined in the SQL
standard."
Humm. Is there a SQL standard to return a subset of the selected tuples?
This being contrary to relational theory I believe. :-)
TOP x isn't in the standard as well, afaik.

I had no doubts there.
Thanks,
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Troels Arvin]

On Mon, 15 Dec 2003 09:32:34 -0800, Roderick A. Anderson wrote:

want to ask if the LIMIT modifier is SQL standard?
No. The standard doesn't have anything like that, unfortunately.
MS SQL Server uses a TOP modifier.

Yes, but it accepts an optional "WITH TIES" modifier which PostgreSQL
doesn't have.

See also:
http://troels.arvin.dk/db/rdbms/#select-limit

--
Greetings from Troels Arvin, Copenhagen, Denmark

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings