Thread: reg:Query

----- Original Message -----

From: Andy Shellam

To: 'sandhya'

Cc: pgsql-admin@postgresql.org

Sent: Tuesday, May 16, 2006 6:26 PM

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 2^nd 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 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 2^nd 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 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;

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.