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

yes, 
> select distinct max(pop),query from test
> group by query
test=# select distinct max(pop),query from bar group by query;
max | query
-----+-------
8 | bar
16 | def
20 | foo
30 | abc

but, I want to get the records contains more than two columns(max,
query, "dfk"), so, if I use group by, max, distinct keywords, I should
use this sql and get the result as below:
test=# select distinct max(pop),query, dfk from bar group by query, dfk;
max | query | dfk
-----+-------+-----
8 | bar | are
10 | abc | 2
15 | foo | fk
16 | def | kj
20 | foo | lk
30 | abc | 1

btw: *distinct on* is useful:)

thanks,

On Fri, 2008-09-19 at 09:00 -0700, Lennin Caro wrote:
>
>
> --- On Fri, 9/19/08, Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com> wrote:
>
> > From: Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com>
> > Subject: [GENERAL] how to return the first record from the sorted
> records which may have duplicated value.
> > To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
> > Date: Friday, September 19, 2008, 8:51 AM
> > 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
> >
> > now, I do it like this(plpgsql)
> > ------------------------------------
> > declare hq := ''::hstore;
> > begin
> > for rc in execute 'select * from test order by pop
> > desc' loop
> > if not defined(hq, rc.query) then
> > hq := hq || (rc.query => '1')::hstore;
> > return next rc;
> > end if;
> > end loop;
> > -----------------------------------
> > language sql/plpgsql will be ok.
> >
> > ps: I try to use "group by" or "max"
> > function, because of the
> > multi-columns(more than 2), I failed.
> >
> > thanks,
> > any answer is appreciated.
> >
> > regards,
> >
>
>
> this query work for me....
>
>
> select distinct max(pop),query from test
> group by query
>
>
> please reply your results
>
> thanks...
>
>
>
>
>