CODEWITHSUNDEEP
sqlpostgresqlselectwindow-functions
4212extra
4212extra

Reputation: 81

Replace Specific values to SQL Rows and Randomly Distribute them

|Value|
|7.5  |
|9    |
|15.5  |
|12.5 |
|6.5  |
|9    |

The Table has lots of numerical values. I want to change all the rows that have 9 to 8.5 and 10.5. If there are 8 values of 9, then 8.5 and 10.5 should be inserted equally and randomly

Upvotes: 0

Views: 71

Answers (1)

GMB
GMB

Reputation: 222482

You could use window functions:

select
    value,
    case rn % 2
        when 0 then 8.5
        when 1 then 10.5
        else value
    end new_value
from (
    select 
        value,
        case when value = 8 
            then row_number() over(partition by value order by rand()) 
        end rn
    from mytable
) t

The inner query randomy ranks records that have value 8 - note that the order by rand() syntax is not consistent across databases (eg it is supported by MySQL, while SQLServer wants order by newid()).

The outer query assigns new values: even ranks get value 8.5, and uneven ranks get 10.5.

The subquery is actually just there to shorten the syntax in the outer query, but is not strictly necessary:

select
    value,
    case 
        when value = 8 and row_number() over(partition by value order by rand()) % 2 = 0 
            then 8.5
        when value = 8 
            then 10.5
        else value
    end new_value
from mytable

Upvotes: 4

Related Questions