Re: An issue with max() and order by ... limit 1 in postgresql8.3-beta3 - Mailing list pgsql-general

zxo102 ouyang <zxo102@gmail.com> wrote:

> Hi everyone,
>    I am using postgresql 8.3-beta3. I have a table 'test' with three fields:

I'm guessing you mean 8.4-beta3, right?


> Any suggestions for the best way to get maximum data value and corresponding
> "time" for each group of sid in my case?

Based on your data:

test=*# select * from test;
 sid | data |        date
-----+------+---------------------
   1 |  1.1 | 2009-09-01 01:00:00
   1 |  2.1 | 2010-01-01 01:00:20
   2 |  3.1 | 2009-09-01 01:00:10
   2 |  0.1 | 2010-01-01 01:00:30
(4 Zeilen)


Try:


Zeit: 0,227 ms
test=*# select distinct on (sid) sid, data, date from test order by sid, data desc, date;
 sid | data |        date
-----+------+---------------------
   1 |  2.1 | 2010-01-01 01:00:20
   2 |  3.1 | 2009-09-01 01:00:10
(2 Zeilen)




Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°