From: | Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | how to return the first record from the sorted records which may have duplicated value. |
Date: | 2008-09-19 08:51:12 |
Message-ID: | 1221814272.3203.29.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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,
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-09-19 08:51:25 | Re: pg_restore questions |
Previous Message | Michael Toews | 2008-09-19 07:53:40 | Re: Synchronize two similar tables: recursive triggers |