Thread: LIMIT modifier

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"

Rod,

Have a look at the bottom of this page:
http://www.postgresql.org/docs/current/static/sql-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

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"

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

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 has no such thing:
http://troels.arvin.dk/db/rdbms/#select-limit

--
Greetings from Troels Arvin, Copenhagen, Denmark