Search Postgresql Archives

how to return the first record from the sorted records which may have duplicated value.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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,



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux