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 <[EMAIL PROTECTED]>: > > 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 <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>: > > > > 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/ > >
