Help with sub query

From: roopa perumalraja <roopabenzer(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Help with sub query
Date: 2007-03-19 02:56:01
Message-ID: 621391.5824.qm@web50808.mail.re2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi all,

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

index_code price_time price

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
.
.
.

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 = 'XYZ' and tm.price_time >= (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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Phillip Smith 2007-03-19 04:03:08 Re: Help with sub query
Previous Message chester c young 2007-03-19 01:21:54 better approach: case or join