Re: how to return the first record from the sorted records which may have duplicated value. - Mailing list pgsql-general

From Yi Zhao
Subject Re: how to return the first record from the sorted records which may have duplicated value.
Date
Msg-id 1222134794.3223.12.camel@localhost.localdomain
Whole thread Raw
In response to Re: how to return the first record from the sorted records which may have duplicated value.  (Lennin Caro <lennin.caro@yahoo.com>)
List pgsql-general
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 <yi.zhao@alibaba-inc.com> wrote:
>
> > From: Yi Zhao <yi.zhao@alibaba-inc.com>
> > 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...
>
>
>
>
>


pgsql-general by date:

Previous
From: "Dale Harris"
Date:
Subject: Re: Triggers not working
Next
From: Bruce Momjian
Date:
Subject: Re: pg_start_backup() takes too long