Обсуждение: Use of the LIMIT clause ?
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
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 ?
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
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
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
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