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