Use of the LIMIT clause ?
Use of the LIMIT clause ?
От:
Richard Bayet <bayet@enseirb.fr>
Дата:
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
Re: Use of the LIMIT clause ?
От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:
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 ?
Re: Use of the LIMIT clause ?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Richard Bayet 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
Re: Use of the LIMIT clause ?
От:
Spy <spy@joystick.fr>
Дата:
Tom Lane a écrit :
>
> Richard Bayet 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
Re: Use of the LIMIT clause ?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Spy 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
Re: Use of the LIMIT clause ?
От:
"pgsql-admin" <pgsql-admin@fc.emc.com.ph>
Дата:
it's like
SELECT *
FROM :table
WHERE :condition
LIMIT :limit
OFFSET :offset
Sherwin
bayet@enseirb.fr writes:
>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster