yes, > select distinct max(pop),query from test > group by query test=# select distinct max(pop),query from bar group by query; max | query -----+------- 8 | bar 16 | def 20 | foo 30 | abc
but, I want to get the records contains more than two columns(max, query, "dfk"), so, if I use group by, max, distinct keywords, I should use this sql and get the result as below: test=# select distinct max(pop),query, dfk from bar group by query, dfk; max | query | dfk -----+-------+----- 8 | bar | are 10 | abc | 2 15 | foo | fk 16 | def | kj 20 | foo | lk 30 | abc | 1 btw: *distinct on* is useful:) thanks, On Fri, 2008-09-19 at 09:00 -0700, Lennin Caro wrote: > > > --- On Fri, 9/19/08, Yi Zhao <[EMAIL PROTECTED]> wrote: > > > From: Yi Zhao <[EMAIL PROTECTED]> > > Subject: [GENERAL] how to return the first record from the sorted > records which may have duplicated value. > > To: "pgsql-general" <pgsql-general@postgresql.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... > > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
