yes, thanks u very much, it's work:)
regards,
Yi
On Fri, 2008-09-19 at 11:06 +0200, Andreas Kretschmer wrote:
> Yi Zhao <yi.zhao@alibaba-inc.com> 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°
>
