Yi Zhao <yi.zhao@xxxxxxxxxxxxxxx> schrieb: > hi all: > I have a table with columns(>2) named "query", "pop", "dfk". > what I want is: > when I do some select, if the column "query" in result records have > duplicate value, I only want the record which have the maximum value of > the "pop". > > for example, the content of table: > query pop dfk > ----------------------- > abc 30 1 --max > foo 20 lk --max > def 16 kj --max > foo 15 fk --discard > abc 10 2 --discard > bar 8 are --max > > the result should be: > query pop dfk > ----------------------- > abc 30 1 > foo 20 lk > def 16 kj > bar 8 are test=*# select * from d; query | pop | dfk -------+-----+----- abc | 30 | 1 foo | 20 | lk def | 16 | kj foo | 15 | fk abc | 10 | 2 bar | 8 | are (6 Zeilen) Zeit: 0,213 ms test=*# select distinct on (query) * from d order by query, pop desc; query | pop | dfk -------+-----+----- abc | 30 | 1 bar | 8 | are def | 16 | kj foo | 20 | lk (4 Zeilen) Hint: distinct on isn't standard-sql, it's an PG-extension. 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°
