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

--- 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...