Sort by multiple columns

Question

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?

There is no direct / convenient way, but I think this is already covered here: http://stackoverflow.com/q/71022/23354 — Marc Gravell, Apr 26 '13 at 06:52

muhmud · Accepted Answer · 2013-04-26T07:22:36.530

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))

Can YILDIZ · Answer 2 · 2013-04-29T09:31:09.913

2

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

edited Apr 29 '13 at 09:31

answered Apr 26 '13 at 07:02

Can YILDIZ

414
3
16

2

That SQL has invalid syntax. – muhmud Apr 26 '13 at 07:10
@HovanGourmet what about muhmud answer. That will also work. He also added sample data in the query. – Maximus Apr 26 '13 at 07:11
@Sivakumar, muhmud answer also seems ok. I marked both as answers (I think). I am new here so not sure how everything works yet. – Hovan Gourmet Apr 26 '13 at 07:14
You can mark only one as an answer. I'm just telling you that his answer also correct. So option is yours. you can mark anything as an answer whichever you feel more comfortable. – Maximus Apr 26 '13 at 07:21
Yeah, just realized that I can mark only one answer. Both answers are ok. Syntax issue with this one is the ",". it shouldn't be there. Didn't try muhmud's answer but that one seems ok too. I will just leave everything as is. Thanks all. – Hovan Gourmet Apr 26 '13 at 07:30
You are right folks, I had a mistake with "," in CASE statement. I corrected it. Thanks. – Can YILDIZ Apr 29 '13 at 09:31

Sort by multiple columns

2 Answers2