Обсуждение: reg:Query
SELECT ….. LIMIT 1
Will get the first row in the dataset. Then to fetch the next row, you can do SELECT …… LIMIT 1 OFFSET 1 (will get the 2nd record) and so on.
(replace ….. with your conditions, LIMIT must come at the end of the query)
Andy
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of sandhya
Sent: 16 May 2006 11:47 am
To: Postgres
Cc: sandhya
Subject: [ADMIN] reg:Query
Hi
Is there any query for possibility of getting only one row at a time in a group of records?
With distinct rows & Columns my query has to return only one record at a time with out any condition.Will it be possible?
Thank you,
Sandhya
!DSPAM:14,4469ca6434491997014324!
----- Original Message -----From: Andy ShellamTo: 'sandhya'Sent: Tuesday, May 16, 2006 6:26 PMSubject: RE: [ADMIN] reg:QuerySELECT .. LIMIT 1
Will get the first row in the dataset. Then to fetch the next row, you can do SELECT LIMIT 1 OFFSET 1 (will get the 2nd record) and so on.
(replace .. with your conditions, LIMIT must come at the end of the query)
Andy
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of sandhya
Sent: 16 May 2006 11:47 am
To: Postgres
Cc: sandhya
Subject: [ADMIN] reg:Query
Hi
Is there any query for possibility of getting only one row at a time in a group of records?
With distinct rows & Columns my query has to return only one record at a time with out any condition.Will it be possible?
Thank you,
Sandhya
!DSPAM:14,4469ca6434491997014324!
----- Original Message -----From: Andy ShellamTo: 'sandhya'Sent: Tuesday, May 16, 2006 6:35 PMSubject: FW: [ADMIN] reg:QueryActually, just realized, this depends on your PGSQL version.
The older format (pre-8.0 I believe) is SELECT .. LIMIT x, y (where x is the number of rows to fetch, and y is the offset (ie. Select x number of rows, starting from row y.)
Andy
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Andy Shellam
Sent: 16 May 2006 1:57 pm
To: 'sandhya'
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] reg:Query
SELECT .. LIMIT 1
Will get the first row in the dataset. Then to fetch the next row, you can do SELECT LIMIT 1 OFFSET 1 (will get the 2nd record) and so on.
(replace .. with your conditions, LIMIT must come at the end of the query)
Andy
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of sandhya
Sent: 16 May 2006 11:47 am
To: Postgres
Cc: sandhya
Subject: [ADMIN] reg:Query
Hi
Is there any query for possibility of getting only one row at a time in a group of records?
With distinct rows & Columns my query has to return only one record at a time with out any condition.Will it be possible?
Thank you,
Sandhya
!DSPAM:14,4469cd4d34491667573013!
Depends on which version of Postgres youre using (pre-8.0 or post-8.0) I think the format changed from v8.0 onwards. So, for example, post-8.0: SELECT email_address FROM account_details ORDER BY address_id LIMIT 1 OFFSET 0; Will bring back the first row. The following will bring back the next and so on: SELECT email_address FROM account_details ORDER BY address_id LIMIT 1 OFFSET 1; Pre-8.0 the queries would be: SELECT email_address FROM account_details ORDER BY address_id LIMIT 1, 0; SELECT email_address FROM account_details ORDER BY address_id LIMIT 1, 1; Note LIMIT 1, 0 (and LIMIT 1 OFFSET 0) is the same as LIMIT 1 Andy ________________________________________ From: sandhya [mailto:sandhyar@amiindia.co.in] Sent: 16 May 2006 2:28 pm To: andy.shellam@mailnetwork.co.uk Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] reg:Query yes..I got it. SELECT .................... LIMIT x OFFSET y.Am i right? ----- Original Message ----- From: Andy Shellam To: 'sandhya' Cc: pgsql-admin@postgresql.org Sent: Tuesday, May 16, 2006 6:35 PM Subject: FW: [ADMIN] reg:Query Actually, just realized, this depends on your PGSQL version. The older format (pre-8.0 I believe) is SELECT .. LIMIT x, y (where x is the number of rows to fetch, and y is the offset (ie. Select x number of rows, starting from row y.) Andy ________________________________________ From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Andy Shellam Sent: 16 May 2006 1:57 pm To: 'sandhya' Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] reg:Query SELECT .. LIMIT 1 Will get the first row in the dataset. Then to fetch the next row, you can do SELECT LIMIT 1 OFFSET 1 (will get the 2nd record) and so on. (replace .. with your conditions, LIMIT must come at the end of the query) Andy ________________________________________ From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of sandhya Sent: 16 May 2006 11:47 am To: Postgres Cc: sandhya Subject: [ADMIN] reg:Query Hi Is there any query for possibility of getting only one row at a time in a group of records? With distinct rows & Columns my query has to return only one record at a time with out any condition.Will it be possible? Thank you, Sandhya !DSPAM:14,4469d1a234495652214946!
Andy Shellam wrote: > Depends on which version of Postgres you’re using (pre-8.0 or post-8.0) > > I think the format changed from v8.0 onwards. So, for example, post-8.0: > > SELECT email_address FROM account_details ORDER BY address_id LIMIT 1 > OFFSET 0; > > Will bring back the first row. The following will bring back the next and > so on: > > SELECT email_address FROM account_details ORDER BY address_id LIMIT > 1 OFFSET 1; > > Pre-8.0 the queries would be: > > SELECT email_address FROM account_details ORDER BY address_id LIMIT > 1, 0; > SELECT email_address FROM account_details ORDER BY address_id LIMIT > 1, 1; Curious; I've never seen this format. I've been using the first for some time now with 7.2.something (Debian Woody), and more recently 7.4.something (Debian Sarge). No problems. Most access is via Perl DBI, but the exact same SQL works just fine from a psql shell. -kgd
Kgd, I think it changed in 8.0 onwards. I used an application that was written for 7.4 on my 8.0.3 server, and it threw up errors at the LIMIT statements, saying the "LIMIT X,Y" format was deprecated, and to use "LIMIT X OFFSET Y" instead - so I had to have much of the application queries rewritten. Ref: section 7.6 in the 8.1-series manual. I'm sure you could use both pre-8.0 - it's just that the LIMIT X,Y format was withdrawn after 8.0. Andy > > Curious; I've never seen this format. I've been using the first for > some time now with 7.2.something (Debian Woody), and more recently > 7.4.something (Debian Sarge). No problems. Most access is via Perl > DBI, but the exact same SQL works just fine from a psql shell. > > -kgd > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > !DSPAM:14,4469e57e34496068418624! >
[Please keep mail on-list; I don't need multiple copies. Thanks.] Andy Shellam wrote: > I think it changed in 8.0 onwards. > > I used an application that was written for 7.4 on my 8.0.3 server, and it > threw up errors at the LIMIT statements, saying the "LIMIT X,Y" format was > deprecated, and to use "LIMIT X OFFSET Y" instead - so I had to have much of > the application queries rewritten. > > Ref: section 7.6 in the 8.1-series manual. > > I'm sure you could use both pre-8.0 - it's just that the LIMIT X,Y format > was withdrawn after 8.0. Er, that's what I meant: I've never seen the LIMIT X,Y format, but I've been using the LIMIT X OFFSET Y format with both 7.2 and 7.4. (Actually, IIRC, this is one of the few bits of code of any kind in my particular app that's still hanging around from before I took over maintenance... when it ran on MySQL. <G> I quickly switched over to Postgres for quite a long list of reasons.) Your earlier messages in the thread seemed to imply that LIMIT X,Y was the ONLY format that would work pre-8.0. Which definitely isn't the case - unless I've been using some mutated third-party-patched versions of Postgres. (Entirely possible given that I usually install distribution packages rather than build-from-source- although I'd be very surprised if this was one of the changes.) -kgd
> Your earlier messages in the thread seemed to imply that LIMIT X,Y was > the ONLY format that would work pre-8.0. I had 2 applications that used the LIMIT X,Y format, and having started out with Postgres on 8.0.0 (only touching base with 7.4), plus coming from a mySQL background (which did use LIMIT X,Y) I wasn't aware you could use both formats in 7.X - but useful to know thanks.