Re: how to return the first record from the sorted records which may have duplicated value.

Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)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°