From: | Lennin Caro <lennin(dot)caro(at)yahoo(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org>, Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com> |
Subject: | Re: how to return the first record from the sorted records which may have duplicated value. |
Date: | 2008-09-19 16:00:54 |
Message-ID: | 666442.74332.qm@web59502.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
--- 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...
From | Date | Subject | |
---|---|---|---|
Next Message | Rainer Bauer | 2008-09-19 16:38:17 | Re: How to change log file language? |
Previous Message | Richard Huxton | 2008-09-19 15:56:46 | Re: setting Postgres client |