pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Use of the LIMIT clause ?
10+ messages / 6 participants
[nested] [flat]

* Use of the LIMIT clause ?
@ 2001-03-13 06:36 Richard Bayet <bayet@enseirb.fr>
  2001-03-12 22:25 ` Re: Use of the LIMIT clause ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 10+ messages in thread

From: Richard Bayet @ 2001-03-13 06:36 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org

I read on a website dedicated to MySQL, that the limit clause allows
someone to restrict the result of a query to a certain domain.
Exemple : "select id, title from movie order by id limit 3" -> only the
(here, first) 3 lines of the table
I did knew about this, but the author goes further about the "limit
N,P", which allows someone to get only results between the Nth and Pth
lines of the whole result...
I tried this with psql, but couldn't get anything but parse errors.
However, "\h select" does mention "LIMIT [count, all] {offset, START}"
So, is it possible or not ? And what's the correct syntax ?

Thanks




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
@ 2001-03-12 22:25 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 10+ messages in thread

From: Stephan Szabo @ 2001-03-12 22:25 UTC (permalink / raw)
  To: Richard Bayet <bayet@enseirb.fr>; +Cc: pgsql-admin@postgresql.org


IIRC, the postgres syntax uses limit and offset for what rows to get
(ie LIMIT 1 OFFSET 10)

On Mon, 12 Mar 2001, Richard Bayet wrote:

> I read on a website dedicated to MySQL, that the limit clause allows
> someone to restrict the result of a query to a certain domain.
> Exemple : "select id, title from movie order by id limit 3" -> only the
> (here, first) 3 lines of the table
> I did knew about this, but the author goes further about the "limit
> N,P", which allows someone to get only results between the Nth and Pth
> lines of the whole result...
> I tried this with psql, but couldn't get anything but parse errors.
> However, "\h select" does mention "LIMIT [count, all] {offset, START}"
> So, is it possible or not ? And what's the correct syntax ?




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
@ 2001-03-12 23:26 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 01:31   ` Re: Use of the LIMIT clause ? Spy <spy@joystick.fr>
  1 sibling, 1 reply; 10+ messages in thread

From: Tom Lane @ 2001-03-12 23:26 UTC (permalink / raw)
  To: Richard Bayet <bayet@enseirb.fr>; +Cc: pgsql-admin@postgresql.org

Richard Bayet <bayet@enseirb.fr> writes:
> I did knew about this, but the author goes further about the "limit
> N,P", which allows someone to get only results between the Nth and Pth
> lines of the whole result...

Is that actually how MySQL interprets two parameters?  We treat them
as count and offset respectively, which definition I thought was the
same as MySQL's.

> I tried this with psql, but couldn't get anything but parse errors.

Works for me:

regression=# select unique1 from tenk1 limit 10;
 unique1
---------
    8800
    1891
    3420
    9850
    7164
    8009
    5057
    6701
    4321
    3043
(10 rows)

regression=# select unique1 from tenk1 limit 3,5;
 unique1
---------
    8009
    5057
    6701
(3 rows)

regression=#

What PG version are you running?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-13 01:31   ` Spy <spy@joystick.fr>
  2001-03-13 02:21     ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Spy @ 2001-03-13 01:31 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@postgresql.org

Tom Lane a écrit :
> 
> Richard Bayet <bayet@enseirb.fr> writes:
> > I did knew about this, but the author goes further about the "limit
> > N,P", which allows someone to get only results between the Nth and Pth
> > lines of the whole result...
> 
> Is that actually how MySQL interprets two parameters?  We treat them
> as count and offset respectively, which definition I thought was the
> same as MySQL's.

Well, it looks like the web site I read the piece of information from
was mistaking...
But MySQL's syntax is different, as found on
http://www.mysql.com/doc/S/E/SELECT.html : 
"SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
[SQL_BUFFER_RESULT]
[...]   
        [LIMIT [offset,] rows]"

> 
> > I tried this with psql, but couldn't get anything but parse errors.
> 
> Works for me:
> 
> regression=# select unique1 from tenk1 limit 10;
>  unique1
> ---------
>     8800
>     1891
>     3420
>     9850
>     7164
>     8009
>     5057
>     6701
>     4321
>     3043
> (10 rows)
> 
> regression=# select unique1 from tenk1 limit 3,5;
>  unique1
> ---------
>     8009
>     5057
>     6701
> (3 rows)
> 
> regression=#
> 
> What PG version are you running?

I'm using v.7.0.2, and the above examples worked for me :)
When I tried using the LIMIT clause the first times, to obtain the same
results, I would have tried "select unique1 from tenk1 limit 5,8", ie
"limit start, stop"
Then I would have got :
  unique1
 ---------
     4321
     3043
 (2 rows)

What I tried and didn't work was the same as "select unique1 from tenk1
limit 5,10" (in my mind, getting the five last rows)...

Thanks for your help

> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 01:31   ` Re: Use of the LIMIT clause ? Spy <spy@joystick.fr>
@ 2001-03-13 02:21     ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 15:07       ` Re: Re: Use of the LIMIT clause ? Richard Poole <richard.poole@vi.net>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2001-03-13 02:21 UTC (permalink / raw)
  To: Spy <spy@joystick.fr>; +Cc: pgsql-admin@postgresql.org; pgsql-sql

Spy <spy@joystick.fr> writes:
> Tom Lane a écrit :
>> Is that actually how MySQL interprets two parameters?  We treat them
>> as count and offset respectively, which definition I thought was the
>> same as MySQL's.

> But MySQL's syntax is different, as found on
> http://www.mysql.com/doc/S/E/SELECT.html : 
> "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> [SQL_BUFFER_RESULT]
> [...]   
>         [LIMIT [offset,] rows]"

That's annoying; looks like we do it backwards from MySQL.  Can anyone
confirm that this is how MySQL behaves (maybe it's a typo on this
documentation page)?

Should we consider changing ours if it is different?  Are there any
other RDBMSes that implement two-parameter LIMIT?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 01:31   ` Re: Use of the LIMIT clause ? Spy <spy@joystick.fr>
  2001-03-13 02:21     ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-13 15:07       ` Richard Poole <richard.poole@vi.net>
  2001-03-20 20:21         ` Re: Re: Use of the LIMIT clause ? Bruce Momjian <pgman@candle.pha.pa.us>
  2001-05-04 22:08         ` Re: Re: Use of the LIMIT clause ? Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 10+ messages in thread

From: Richard Poole @ 2001-03-13 15:07 UTC (permalink / raw)
  To: pgsql-sql

On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote:
> Spy <spy@joystick.fr> writes:
> > Tom Lane a écrit :
> >> Is that actually how MySQL interprets two parameters?  We treat them
> >> as count and offset respectively, which definition I thought was the
> >> same as MySQL's.
> 
> > But MySQL's syntax is different, as found on
> > http://www.mysql.com/doc/S/E/SELECT.html : 
> > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> > [SQL_BUFFER_RESULT]
> > [...]   
> >         [LIMIT [offset,] rows]"
> 
> That's annoying; looks like we do it backwards from MySQL.  Can anyone
> confirm that this is how MySQL behaves (maybe it's a typo on this
> documentation page)?

Yes, it does behave as documented.

> Should we consider changing ours if it is different?

I don't know that it's worth it... it seems to inconvenience some
people either way. I may soon be moving a moderately complex system
from MySQL to Postgres and it wouldn't be the end of my world if
I had to reverse all the LIMITs.

Richard



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 01:31   ` Re: Use of the LIMIT clause ? Spy <spy@joystick.fr>
  2001-03-13 02:21     ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 15:07       ` Re: Re: Use of the LIMIT clause ? Richard Poole <richard.poole@vi.net>
@ 2001-03-20 20:21         ` Bruce Momjian <pgman@candle.pha.pa.us>
  2001-03-20 21:16           ` Re: Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 10+ messages in thread

From: Bruce Momjian @ 2001-03-20 20:21 UTC (permalink / raw)
  To: Richard Poole <richard.poole@vi.net>; +Cc: pgsql-sql


Is this a TODO item?  Seems we should match MySQL, seeing as we took the
feature syntax from them.

[ Charset ISO-8859-1 unsupported, converting... ]
> On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote:
> > Spy <spy@joystick.fr> writes:
> > > Tom Lane a ?crit :
> > >> Is that actually how MySQL interprets two parameters?  We treat them
> > >> as count and offset respectively, which definition I thought was the
> > >> same as MySQL's.
> > 
> > > But MySQL's syntax is different, as found on
> > > http://www.mysql.com/doc/S/E/SELECT.html : 
> > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> > > [SQL_BUFFER_RESULT]
> > > [...]   
> > >         [LIMIT [offset,] rows]"
> > 
> > That's annoying; looks like we do it backwards from MySQL.  Can anyone
> > confirm that this is how MySQL behaves (maybe it's a typo on this
> > documentation page)?
> 
> Yes, it does behave as documented.
> 
> > Should we consider changing ours if it is different?
> 
> I don't know that it's worth it... it seems to inconvenience some
> people either way. I may soon be moving a moderately complex system
> from MySQL to Postgres and it wouldn't be the end of my world if
> I had to reverse all the LIMITs.
> 
> Richard
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 01:31   ` Re: Use of the LIMIT clause ? Spy <spy@joystick.fr>
  2001-03-13 02:21     ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 15:07       ` Re: Re: Use of the LIMIT clause ? Richard Poole <richard.poole@vi.net>
  2001-03-20 20:21         ` Re: Re: Use of the LIMIT clause ? Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-03-20 21:16           ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-20 21:19             ` Re: Re: Use of the LIMIT clause ? Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2001-03-20 21:16 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Richard Poole <richard.poole@vi.net>; pgsql-sql

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is this a TODO item?  Seems we should match MySQL, seeing as we took the
> feature syntax from them.

Question is, will more people be inconvenienced if we change it than if
we leave it alone?  Seems like someone gets screwed either way ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 01:31   ` Re: Use of the LIMIT clause ? Spy <spy@joystick.fr>
  2001-03-13 02:21     ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 15:07       ` Re: Re: Use of the LIMIT clause ? Richard Poole <richard.poole@vi.net>
  2001-03-20 20:21         ` Re: Re: Use of the LIMIT clause ? Bruce Momjian <pgman@candle.pha.pa.us>
  2001-03-20 21:16           ` Re: Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-20 21:19             ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 10+ messages in thread

From: Bruce Momjian @ 2001-03-20 21:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Poole <richard.poole@vi.net>; pgsql-sql

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Is this a TODO item?  Seems we should match MySQL, seeing as we took the
> > feature syntax from them.
> 
> Question is, will more people be inconvenienced if we change it than if
> we leave it alone?  Seems like someone gets screwed either way ...

No question about it.  One solution would be to disable this syntax for
one release and then fix it in the one after that.  We already offer
LIMIT/OFFSET, which is what they should be using anyway.  If we can't
remember, and no one reported the bug for many releases, odds are no one
was using that more limited form anyway.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Re: Use of the LIMIT clause ?
  2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
  2001-03-12 23:26 ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 01:31   ` Re: Use of the LIMIT clause ? Spy <spy@joystick.fr>
  2001-03-13 02:21     ` Re: Use of the LIMIT clause ? Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-13 15:07       ` Re: Re: Use of the LIMIT clause ? Richard Poole <richard.poole@vi.net>
@ 2001-05-04 22:08         ` Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 10+ messages in thread

From: Bruce Momjian @ 2001-05-04 22:08 UTC (permalink / raw)
  To: Richard Poole <richard.poole@vi.net>; +Cc: pgsql-sql

[ Charset ISO-8859-1 unsupported, converting... ]
> On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote:
> > Spy <spy@joystick.fr> writes:
> > > Tom Lane a ?crit :
> > >> Is that actually how MySQL interprets two parameters?  We treat them
> > >> as count and offset respectively, which definition I thought was the
> > >> same as MySQL's.
> > 
> > > But MySQL's syntax is different, as found on
> > > http://www.mysql.com/doc/S/E/SELECT.html : 
> > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT]
> > > [SQL_BUFFER_RESULT]
> > > [...]   
> > >         [LIMIT [offset,] rows]"
> > 
> > That's annoying; looks like we do it backwards from MySQL.  Can anyone
> > confirm that this is how MySQL behaves (maybe it's a typo on this
> > documentation page)?
> 
> Yes, it does behave as documented.
> 
> > Should we consider changing ours if it is different?
> 
> I don't know that it's worth it... it seems to inconvenience some
> people either way. I may soon be moving a moderately complex system
> from MySQL to Postgres and it wouldn't be the end of my world if
> I had to reverse all the LIMITs.
> 

Added to TODO.  If we took the feature from MySQL, seems we should match
it.  This will require a clear notice in the release notes:

	* Change LIMIT val,val to be offset,limit to match MySQL   

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2001-05-04 22:08 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-13 06:36 Use of the LIMIT clause ? Richard Bayet <bayet@enseirb.fr>
2001-03-12 22:25 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-12 23:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-13 01:31   ` Spy <spy@joystick.fr>
2001-03-13 02:21     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-13 15:07       ` Richard Poole <richard.poole@vi.net>
2001-03-20 20:21         ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-20 21:16           ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-20 21:19             ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-05-04 22:08         ` Bruce Momjian <pgman@candle.pha.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox