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

From: Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: how to return the first record from the sorted records which may have duplicated value.
Date: 2008-09-19 09:10:58
Message-ID: 1221815458.3203.33.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Glyn Astill 2008-09-19 09:14:21 Stop trigger fireing for a specific user?
Previous Message Andreas Kretschmer 2008-09-19 09:06:32 Re: how to return the first record from the sorted records which may have duplicated value.