yes, thanks u very much, it's work:) regards, Yi On Fri, 2008-09-19 at 11:06 +0200, Andreas Kretschmer wrote: > Yi Zhao <[EMAIL PROTECTED]> 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° >
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
