Mailbox-Line: From teknokrat@yahoo.com  Thu Jul  4 17:07:04 2002
From: teknokrat@yahoo.com (teknokrat)
Subject: Re: Can this be done with sql?
Date: 4 Jul 2002 14:05:58 -0700
Organization: http://groups.google.com/
Lines: 59
Message-ID: <1cbae208.0207041305.43dffc0@posting.google.com>
References: <1cbae208.0207031032.7d1be1a2@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

""Rajesh Kumar Mallah."" <mallah@trade-india.com> wrote in message
news:200207042020.29657.mallah@trade-india.com...
> Hi ,
>
> you can use GROUP BY , at the expense of adding one more column of SERIAL
d=
> ata type,
>
> say,
>
> select * from t_a  limit 10;
> access_log=3D# SELECT * from t_a   limit 15;
>
>  sno | value
> -----+-------
>    1 |  4533
>    2 |  2740
>    3 |  9970
>
>    4 |  6445
>    5 |  2220
>    6 |  2301
>
>    7 |  6847
>    8 |  5739
>    9 |  5286
>
>   10 |  5556
>   11 |  9309
>   12 |  9552
>
>   13 |  8589
>   14 |  5935
>   15 |  2382
> (15 rows)
>
> if you want avg for every third item you can use:
>
> access_log=3D# SELECT avg(value) from t_a group by (1+(sno-1)/3)  limit
5;=
> =20=20
>

yes, thank you, that may help but unfortunately there are are few more
problems to face.

1. I will need to select groups from anywhere in the table so i cannot
assume that 1 will be the start number. They will be contigous however so i
can use another query top get the start number but is it possible to do it
with just one select?

2. I need to display not just aggregates but the first and last value in the
group for two of the fields. I mean by this that i need
opening_value(field1) and closing_value(field2).

3. If this needs to be done via stored procedure how do i get it to return a
result set. I've tried setof record but it doesn't work.

thanks