CODEWITHSUNDEEP
.netsql-servert-sql
Hovan Gourmet
Hovan Gourmet

Reputation: 97

Sort by multiple columns

I have a database and need a more or less simple select statement which needs to be ordered by 4 columns. I don't need "order by field1,field2,field3,field4" but I need to sort from lowest to highest value those 4 fields, wherever the value is.

So for instance, if I have data similar to this

Field1    Field2     Field3      Field4
---------------------------------------
   3         4           6           7
   9         5           4           1
   5         4           8           2
   6         4           4           5

Data should be sorted like this:

Field1    Field2     Field3      Field4
---------------------------------------
   9         5           4           1
   5         4           8           2
   3         4           6           7
   6         4           4           5

So, 1 is contained in field 4 and that is the lowest value, so that row goes first, same goes for the number 2 which is also in column 4, after that, we have a number 3 in column one so that row goes next and final row is number 4 in cols 2 and 3. I am not sure if I explained the issue well, so let me know if further explanation is needed.

Is there a way to do this directly using mssql, or do I need to sort datatable in actual code?

Upvotes: 2

Views: 116

Answers (2)

Can YILDIZ
Can YILDIZ

Reputation: 413

You can use following:

SELECT *,
  CASE WHEN Field1 < Field2 AND Field1 < Field3 AND Field1 < Field4 THEN Field1
       WHEN Field2 < Field1 AND Field2 < Field3 AND Field2 < Field4 THEN Field2
       WHEN Field3 < Field1 AND Field3 < Field2 AND Field3 < Field4 THEN Field3
       ELSE Field4 END
       as SORTFIELD
from table
ORDER BY SORTFIELD

Upvotes: 2

muhmud
muhmud

Reputation: 4604

with data as (
    select *
    from (
    values (   3         ,4           ,6           ,7),
       (9         ,5           ,4           ,1),
       (5         ,4           ,8           ,2),
       (6         ,4           ,4           ,5)
    ) t (Field1    ,Field2     ,Field3      ,Field4)
)
select * from data
order by (case when Field1 < Field2 and Field1 < Field3 and Field1 < Field4 then Field1
                when Field2 < Field1 and Field2 < Field3 and Field1 < Field4 then Field2
                when Field3 < Field1 and Field3 < Field2 and Field1 < Field4 then Field3
                else Field4 end)

Also, a more extensible way:

with data as (
    select *
    from (
    values (   3         ,4           ,6           ,7),
       (9         ,5           ,4           ,1),
       (5         ,4           ,8           ,2),
       (6         ,4           ,4           ,5)
    ) t (Field1    ,Field2     ,Field3      ,Field4)
)
select d.*
from data d
order by (select min(Field) 
          from (values (Field1), (Field2), (Field3), (Field4)) t(Field))

Upvotes: 3

Related Questions