CODEWITHSUNDEEP
sqlsql-serverdatabasefunction
Ger Mc
Ger Mc

Reputation: 640

Find lowest values from multiple Columns?

How could I select the lowest 3 numbers from multiple columns?

id          num1        num2        num3        num4        num5
----------- ----------- ----------- ----------- ----------- -----------
1           1           6           5           1           2
2           6           2           2           3           2
3           2           1           2           1           5
4           4           1           1           5           7
5           5           1           4           7           8

For example

(lowest 2 values from id 1) > ((lowest 2 values from id 5)

Upvotes: 0

Views: 56

Answers (2)

Rahul
Rahul

Reputation: 77866

Do a order by desc and select top of the rows like

select top 3 num1, num2, num3
from tbl1
order by num1 desc, num2 desc, num3 desc

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method uses cross apply:

select t.id, v.num
from t cross apply
     (select top 3 num
      from (values (num1), (num2), (num3), (num4), (num5)) v(num)
      order by num asc
     ) v;

If you want them in the same row, then re-pivot:

select t.id, v.*
from t cross apply
     (select max(case when seqnum = 1 then num end) as num1,
             max(case when seqnum = 2 then num end) as num2,
             max(case when seqnum = 3 then num end) as num3                 
      from (select num, row_number() over (order by num) as seqnum
            from (values (num1), (num2), (num3), (num4), (num5)) v(num)
           ) v
      ) v;

Upvotes: 3

Related Questions