CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Paresh
Paresh

Reputation: 3749

How to get column which has least values among them

CREATE TABLE exmp_test
(
id int, 
v1 int,
v2 int,
v3 int,
v4 int
)

SELECT * FROM exmp_test

id  v1  v2  v3  v4
1   2   4   6   7
1   4   77  3   8

I want to add the value of the [id] column to (whichever has least value for v1 ,v2 ,v3 ,v4) for each row.

As an example, for the first row, the [id] value should be add to v1 (because it has the lowest value). For the second row, the [id] value should be add to v3 (because it has the lowest value).

How would I write the SQL to do this?

Upvotes: 1

Views: 610

Answers (2)

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

You can do the same using UVPIVOT

Test data

declare @exmp_test table(id int, v1 int,v2 int,v3 int,v4 int)
insert into @exmp_test 
 select 1,   2 ,  4 ,  6,   7 union all
 select 1 ,  4 ,  77 , 3  , 8 union all
 select 2 ,  4 ,  16 , 1  , 8

Query

;with cte as(select row_number() over(order by id) as rn,t.v1 + t.v2 + t.v3 + t.v4 as sumallversions,t.* from @exmp_test t)
    ,unpvtcte as(select rn,id as versions,vals from (select rn,[v1],[v2],[v3],[v4] from cte)t
    unpivot (vals for id in([v1],[v2],[v3],[v4]))as unpvt)
    update @exmp_test
    set id = y.sumall
    from @exmp_test e
    join (
    select c.id,c.id + x.minvals as sumall,c.sumallversions, x.minvals from cte c 
    join(select rn,MIN(vals) minvals from unpvtcte group by rn)x on x.rn = c.rn) y
    on y.id = e.id
    and y.sumallversions = e.v1 + e.v2 + e.v3 + e.v4

Output:

id v1 v2 v3 v4
3 2 4 6 7
4 4 77 3 8
3 4 16 1 8

Upvotes: 1

Andomar
Andomar

Reputation: 238078

You could normalize the table in a CTE (common table expression), and then select the row with the minimum value. Based on the comment below your question, I've added priority for v1.

;with Normal as (
    select id, v1 as value, 1 as prio from YourTable
    union all select id, v2, 2 as prio from YourTable
    union all select id, v3, 3 as prio from YourTable
    union all select id, v4, 4 as prio from YourTable
)
select top 1 id, value
from Normal
where value = (
    select min(value) from Normal
)
order by prio

After re-reading your question, here's a way to look at the lowest value per row and add the id field to that:

update t1
set v1 = v1 + case when mincol = 1 then id else 0 end
,   v2 = v2 + case when mincol = 2 then id else 0 end
,   v3 = v3 + case when mincol = 3 then id else 0 end
,   v4 = v4 + case when mincol = 4 then id else 0 end
from (
    select id, v1, v2, v3, v4,
        case 
          when v1 <= v2 and v1 <= v3 and v1 <= v4 then 1
          when v2 <= v3 and v2 <= v4 then 2
          when v3 <= v4 then 3
          else 4
         end as mincol
    from YourTable
) t1

Upvotes: 2

Related Questions