CODEWITHSUNDEEP
sqlsql-server
user1482489
user1482489

Reputation: 25

sort order in sql with 2 columns

I have table like this:

+----+------+-----------+-------------------+
| ID | Name | sortorder | overridesortorder |
+----+------+-----------+-------------------+
| 1  | kkkk | 4         | null              |
+----+------+-----------+-------------------+
| 2  | yyyy | 3         | null              |
+----+------+-----------+-------------------+
| 3  | zzzz | 2         | 4                 |
+----+------+-----------+-------------------+
| 4  | gggg | 1         | 3                 |
+----+------+-----------+-------------------+

I need to display the order like below, sorting the table with above two sort order columns:

+----+-------+-----------+-------------------+
| ID | Name  | sortorder | overridesortorder |
+----+-------+-----------+-------------------+
| 1  | kkkk  | **4**     | null              |
+----+-------+-----------+-------------------+
| 3  | zzzz  | 2         | **4**             |
+----+-------+-----------+-------------------+
| 2  | yyyy  | **3**     | null              |
+----+-------+-----------+-------------------+
| 4  | gggg  | 1         | **3**             |
+----+-------+-----------+-------------------+

Upvotes: 1

Views: 150

Answers (5)

p.star
p.star

Reputation: 1

I am adding few more rows

in this case if the sortorder and overridesortorder is null i need to display order by Name

ID  Name    sortorder   OverriderSortOrder  order_value
3   zzzz    2   4   4
1   kkkk    4   NULL    4
2   yyyy    3   NULL    3
4   gggg    1   3   3
5   hhhh    NULL    NULL    NULL
6   bbbb    NULL    NULL    NULL
7   jjjj    NULL    NULL    NULL
8   cccc    NULL    NULL    NULL

Upvotes: 0

Andrew
Andrew

Reputation: 1596

SELECT ID, Name, sortorder, overridesortorder 
FROM t
ORDER BY COALESCE(overridessortorder, sortorder) DESC, sortorder DESC;

This will combine the two columns, using the override where it is not null, then sort order where it is null, and then sort the result based on the original sort order column to break ties. If you want to break ties based on the override, you just switch that second sort.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

This is what you are looking is 

select * 
from table
order by isnull(overridesortorder, sortorder) desc, overridesortorder

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Is this what you want?

select t.*
from t
order by coalesce(overridessortorder, sortorder) desc;

Upvotes: 1

Wesgur
Wesgur

Reputation: 3227

Get the maximum value of the two columns and order by it with that

SELECT ID, Name, sortorder, overridesortorder,
(SELECT Max(o) 
FROM (VALUES (sortorder), (overridesortorder)) AS value(o)) as order_value
FROM [TABLE] ORDER BY order_value

Upvotes: 0

Related Questions