Re: (un)grouping question

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@j-davis.com <mailto:pgsql@j-davis.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/