Solution to retrieve first and last row for each minute - Mailing list pgsql-sql

Hi all,

 

I have a table trans with the data

 

 price | volume | date  |  time  
-------+--------+------------+--------------
 79.87 |    500 | 2006-06-01 | 13:30:14.262
 79.87 |    900 | 2006-06-01 | 13:30:15.375
  79.85 |    200 | 2006-06-01 | 13:30:17.381
 79.85 |    500 | 2006-06-01 | 13:30:20.276
 79.85 |   1900 | 2006-06-01 | 13:30:21.438
 79.83 |    200 | 2006-06-01 | 13:30:23.388
 79.83 |    600 | 2006-06-01 | 13:30:25.863
 79.82 |    400 | 2006-06-01 | 13:30:27.838
 79.82 |    400 | 2006-06-01 | 13:30:29.314
 79.82 |    400 | 2006-06-01 | 13:30:30.776
 79.81 |    400 | 2006-06-01 | 13:30:31.867
 79.82 |    100 | 2006-06-01 | 13:30:32.446
  79.82 |    100 | 2006-06-01 | 13:30:41.44
 79.82 |    100 | 2006-06-01 | 13:30:42.712
 79.82 |    400 | 2006-06-01 | 13:30:45.907
 79.83 |    600 | 2006-06-01 | 13:30:48.245
 79.83 |    400 | 2006-06-01 | 13:30:49.33
 79.85 |    100 | 2006-06-01 | 13:30:53.282
 79.89 |    700 | 2006-06-01 | 13:31:06.437
 79.89 |   1100 | 2006-06-01 | 13:31:08.513
 79.89 |    100 | 2006-06-01 | 13:31:12.318
 79.89 |   2200 | 2006-06-01 | 13:31:16.867
 79.89 |   2400 | 2006-06-01 | 13:31:19.832
 79.89 |   1200 | 2006-06-01 | 13:31:22.361
 79.89 |   1000 | 2006-06-01 | 13:31:34.93
 79.88 |    600 | 2006-06-01 | 13:31:44.98
 79.89 |   3200 | 2006-06-01 | 13:31:46.497
 79.88 |   1100 | 2006-06-01 | 13:31:49.345
 79.88 |    500 | 2006-06-01 | 13:31:52.362
 79.88 |    300 | 2006-06-01 | 13:31:53.286
 79.85 |    800 | 2006-06-01 | 13:31:54.309
 79.84 |   1900 | 2006-06-01 | 13:31:55.834
 79.84 |    100 | 2006-06-01 | 13:32:02.318
 79.85 |    700 | 2006-06-01 | 13:32:05.975
 79.84 |    600 | 2006-06-01 | 13:32:06.375
 79.84 |    500 | 2006-06-01 | 13:32:07.904
 79.85 |    500 | 2006-06-01 | 13:32:08.918
 79.87 |    400 | 2006-06-01 | 13:32:18.782
 79.88 |    200 | 2006-06-01 | 13:32:20.336
 79.88 |   1600 | 2006-06-01 | 13:32:30.381
 79.88 |    200 | 2006-06-01 | 13:32:34.912
 79.88 |    700 | 2006-06-01 | 13:32:36.279
 79.88 |    100 | 2006-06-01 | 13:32:36.806
 79.88 |   1500 | 2006-06-01 | 13:32:38.795
  79.9 |    400 | 2006-06-01 | 13:32:40.992
  79.9 |    200 | 2006-06-01 | 13:32:49.892
  79.9 |    400 | 2006-06-01 | 13:32:51.391
  79.9 |    200 | 2006-06-01 | 13:33:00.274
 79.91 |    100 | 2006-06-01 | 13:33:03.862
 79.92 |    200 | 2006-06-01 | 13:33:11.787
 79.91 |    500 | 2006-06-01 | 13:33:12.781
 79.91 |   1000 | 2006-06-01 | 13:33:12.781
 79.95 |   1400 | 2006-06-01 | 13:33:14.962
 79.94 |   1000 | 2006-06-01 | 13:33:17.429
 79.95 |    200 | 2006-06-01 | 13:33:19.865
 79.93 |    200 | 2006-06-01 | 13:33:20.91
 79.93 |    200 | 2006-06-01 | 13:33:21.281
 79.93 |   2200 | 2006-06-01 | 13:33:24.363
 79.93 |    600 | 2006-06-01 | 13:33:25.739
 79.94 |    200 | 2006-06-01 | 13:33:27.436
 79.93 |   1300 | 2006-06-01 | 13:33:29.375
 79.93 |    600 | 2006-06-01 | 13:33:30.375
 79.93 |    300 | 2006-06-01 | 13:33:32.352
 79.92 |   1400 | 2006-06-01 | 13:33:33.279
 79.93 |    200 | 2006-06-01 | 13:33:34.825
 :

 :

 :

 

I need to get the first and last price per every minute along with count, average, maximum, minumum of the price and sum of the volume . Right now I have my query which calculates count, maximum, minimum and average.

 

 select trnew.date, trnew.trunc_time, count(*) as count, avg(trnew.price) as avg_price,
       sum(trnew.price*trnew.volume)/sum(trnew.volume) as vwap,
       max(trnew.price) as high_price, min(trnew.price) as low_price,
       sum(trnew.volume) as sum_volume from (select tr.date,
       date_trunc('minute', tr.time) - interval '4 hour' as trunc_time,
       tr.price, tr.volume from trans tr
       where tr.time between '13:30:00.00' and '20:00:0.00' ) as trnew
       group by trnew.date, trnew.trunc_time order by trnew.date, trnew.trunc_time;

 

How do I add first and last price for each minute to this query?

 

Thanks a lot in advance.

 

Cheers.