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

now, I do it like this(plpgsql)
-----------
this methold have low efficiency, when the records is large, it will
become slow, so someone can tell me some high efficiency way???

thanks.
On Fri, 2008-09-19 at 16:51 +0800, Yi Zhao wrote:

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