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

Поиск
Список
Период
Сортировка
От Yi Zhao
Тема how to return the first record from the sorted records which may have duplicated value.
Дата
Msg-id 1221814272.3203.29.camel@localhost.localdomain
обсуждение исходный текст
Ответ на Re: Converting string to IN query  (Harald Fuchs <hari.fuchs@gmail.com>)
Ответы Re: how to return the first record from the sorted records which may have duplicated value.  (Andreas Kretschmer <akretschmer@spamfence.net>)
Re: how to return the first record from the sorted records which may have duplicated value.  (Yi Zhao <yi.zhao@alibaba-inc.com>)
Re: how to return the first record from the sorted records which may have duplicated value.  (Lennin Caro <lennin.caro@yahoo.com>)
Список 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,


В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Toews
Дата:
Сообщение: Re: Synchronize two similar tables: recursive triggers
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: pg_restore questions