Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7905C9FB3D2 for ; Mon, 19 Mar 2007 01:03:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 97075-07 for ; Mon, 19 Mar 2007 01:03:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from weatherbeeta.com.au (218-214-212-153.people.net.au [218.214.212.153]) by postgresql.org (Postfix) with SMTP id 89A2E9FB3B5 for ; Mon, 19 Mar 2007 01:03:13 -0300 (ADT) Received: (qmail 14098 invoked from network); 19 Mar 2007 04:03:11 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;fbf1613688445bdab54b45737b99716b; Received: from mail.weatherbeeta.com.au (10.10.0.2) by network-box.weatherbeeta.com.au with SMTP; 19 Mar 2007 04:03:11 -0000 Received: (qmail 3902 invoked by uid 1166); 19 Mar 2007 15:03:10 +1100 Received: from 172.20.0.155 by mail.weatherbeeta.com.au (envelope-from , uid 1165) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.6/2315. perlscan: 1.25-st-qms. Clear:RC:1(172.20.0.155):. Processed in 0.996265 secs); 19 Mar 2007 04:03:10 -0000 X-Antivirus-mail.weatherbeeta.com.au-Mail-From: phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):. Processed in 0.996265 secs Process 3889) Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155) by mail.weatherbeeta.com.au with SMTP; 19 Mar 2007 15:03:08 +1100 From: "Phillip Smith" To: "'roopa perumalraja'" , Subject: Re: Help with sub query Date: Mon, 19 Mar 2007 15:03:08 +1100 Organization: Weatherbeeta Pty Ptd Message-ID: <02f901c769db$8122bc00$9b0014ac@wbaus090> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_02FA_01C76A37.B4933400" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 In-Reply-To: <621391.5824.qm@web50808.mail.re2.yahoo.com> Importance: Normal X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1174276991.200-14089-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.06/0.20 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.077 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200703/242 X-Sequence-Number: 27880 This is a multi-part message in MIME format. ------=_NextPart_000_02FA_01C76A37.B4933400 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Add something like this to your query: =20 ORDER BY price_time LIMIT 1 =20 Cheers, ~p =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of roopa perumalraja Sent: Monday, 19 March 2007 13:56 To: pgsql-sql@postgresql.org Subject: [SQL] Help with sub query =20 Hi all, =20 I want the last value from the group of rows. The table 'index_prices' = is =20 index_code price_time price =20 XYZ 09:45:00 7.5 XYZ 09:46:00 7.4 XYZ 09:59:00 7.2 XYZ 10:00:00 7.3 XYZ 10:01:00 7.6 XYZ 10:02:00 7.3 . . . =20 The other table 'times' is for time interval =20 snapshot_time =20 10:00:00 10:30:00 11:00:00 =20 I want the last value of the price between the interval of time. So I do = =20 select tm.index_code, tm.price_date, t.snapshot_time, last(tm.price) from index_prices_200703 tm, times t where tm.index_code =3D 'XYZ' and tm.price_time >=3D (t.snapshot_time - '30 minute' :: interval)::time and tm.price_time < t.snapshot_time =20 group by t.snapshot_time, tm.price_date, tm.index_code order by tm.index_code, tm.price_date, t.snapshot_time;=20 =20 The last() doesn't work. Can this be done using sub query? =20 Thanks in advance =20 _____ =20 Food fight? Enjoy some healthy debate in the Yahoo! Answers Food & Drink Q&A. =0A*******************Confidentiality and Privilege Notice*******************= =0A=0AThe material contained in this message is privileged and confidential to= =0Athe addressee. If you are not the addressee indicated in this message or= =0Aresponsible for delivery of the message to such person, you may not copy=0Aor= deliver this message to anyone, and you should destroy it and kindly=0Anotify= the sender by reply email.=0A=0AInformation in this message that does not relat= e to the official business=0Aof Weatherbeeta must be treated as neither given = nor endorsed by Weatherbeeta.=0AWeatherbeeta, its employees, contractors or as= sociates shall not be liable=0Afor direct, indirect or consequential loss aris= ing from transmission of this=0Amessage or any attachments=0A ------=_NextPart_000_02FA_01C76A37.B4933400 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Add something like this to your = query:

 

ORDER BY price_time LIMIT = 1

 

Cheers,

~p

 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of roopa perumalraja
Sent: Monday, 19 March = 2007 13:56
To: = pgsql-sql@postgresql.org
Subject: [SQL] Help with = sub query

 

Hi = all,

 

I want = the last value from the group of rows. The table 'index_prices' = is

 

index_code price_time price

 

XYZ       &= nbsp;    09:45:00   7.5

XYZ       &= nbsp;    09:46:00   7.4

XYZ       &= nbsp;    09:59:00   7.2

XYZ       &= nbsp;    10:00:00   7.3

XYZ       &= nbsp;    10:01:00   7.6

XYZ       &= nbsp;    10:02:00   7.3

.

.

.

 

The other = table 'times' is for time interval

 

snapshot_time

 

10:00:00

10:30:00

11:00:00

 

I want the = last value of the price between the interval of time. So I do

 

select = tm.index_code, tm.price_date, t.snapshot_time, last(tm.price)
from index_prices_200703 tm, times t where tm.index_code =3D 'XYZ' and tm.price_time >=3D (t.snapshot_time - '30 minute' :: interval)::time = and tm.price_time < t.snapshot_time 
group by t.snapshot_time, tm.price_date, tm.index_code order by = tm.index_code, tm.price_date, t.snapshot_time;

 

The last() = doesn't work. Can this be done using sub query?

 

Thanks in = advance

 

Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.


=

=0A*******************Confidentiality and Privilege Notice*************= ******=0A

=0AThe material contained in this message is privileged and conf= idential to=0Athe addressee. If you are not the addressee indicated in this m= essage or=0Aresponsible for delivery of the message to such person, you may no= t copy=0Aor deliver this message to anyone, and you should destroy it and kind= ly=0Anotify the sender by reply email.=0A

=0AInformation in this message that do= es not relate to the official business=0Aof Weatherbeeta must be treated as ne= ither given nor endorsed by Weatherbeeta.=0AWeatherbeeta, its employees, contr= actors or associates shall not be liable=0Afor direct, indirect or consequenti= al loss arising from transmission of this=0Amessage or any attachments=0A
= ------=_NextPart_000_02FA_01C76A37.B4933400--