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@xxxxxxxxxxx <mailto:pgsql@xxxxxxxxxxx>>:
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/
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
