--- On Fri, 9/19/08, Yi Zhao <yi.zhao@xxxxxxxxxxxxxxx> wrote: > From: Yi Zhao <yi.zhao@xxxxxxxxxxxxxxx> > Subject: [GENERAL] how to return the first record from the sorted records which may have duplicated value. > To: "pgsql-general" <pgsql-general@xxxxxxxxxxxxxx> > 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...
