By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

LIMIT modifier

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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)

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.