Re: (un)grouping question

Great, this does the trick thanks!!

um... somevalue+random() is a simplified version of what I really wanted to
do, i just wante the general idea of what the query would look like.

2008/1/21, Andrei Kovalevski <andyk(at)commandprompt(dot)com>:
>
> May be this is what you need:
>
> select
> test.uid, coalesce(t.somevalue + a.max + t.uid, test.somevalue)
> from
> test
> left outer join
> (select
> *
> from
> test
> where
> (uid, somevalue) not in
> (select min(uid), somevalue from test group by somevalue)
> ) t on (test.uid = t.uid),
> (select max(somevalue) from test) a
>
> Rhys Stewart wrote:
> > ok, let me clarify, dont want to remove them just want them changed
> > but need to keep the uid. However, I would like just one somevalue to
> > remain the same. so for example, uids, 2,4 and 8 have somevalue 44,
> > after i would like 2 to remain 44 but uids 4 and 8 would be changed.
> > 2008/1/21, Jeff Davis <pgsql(at)j-davis(dot)com <mailto:pgsql(at)j-davis(dot)com>>:
> >
> > On Mon, 2008-01-21 at 12:36 -0500, Rhys Stewart wrote:
> > > Hi list,
> > >
> > > have the following table
> > >
> > > uid|somevalue
> > > --------------------
> > > 1|11
> > > 2|44
> > > 3|31
> > > 4|44
> > > 5|71
> > > 6|33
> > > 7|33
> > > 8|44
> > > 9|14
> > >
> > > would like to remove the duplicate values in the column somevalue.
> > > doing this by just adding a random number is perfectly fine,
> > however
> > > i want to retain at least one of the original values of
> > somevalue. Any
> > > ideas how to do this in in a query?
> >
> > Would something like this help?
> >
> > SELECT MIN(uid), somevalue FROM mytable GROUP BY somevalue;
> >
> > Also consider just doing:
> >
> > SELECT DISTINCT somevalue FROM mytable;
> >
> > ...if you don't need uid in the result set.
> >
> > Regards,
> > Jeff Davis
> >
> >
>
>
> --
> Andrei Kovalevski
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> Managed Services, Shared and Dedicated Hosting
> Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
>
>