SQL Server Order SELECT List with condition

Question

I want order the select list with condition. For example; I have t1 table and it has 3 columns and 6 rows

Table t1: 
+----+---+---+
| id | x | y |
+----+---+---+
| 1 | a | 1 |
| 2 | b | 3 |
| 3 | c | 2 |
| 4 | d | 2 |
| 5 | e | 1 |
| 6 | f | 3 |
+----+---+---+

Ordering the select list without condition, that select query is easy:
SELECT y FROM t1 ORDER BY y
result: 1,1,2,2,3,3
SELECT y FROM t1 ORDER BY y DESC
result: 3,3,2,2,1,1

If I want that result 2,2,1,1,3,3 or 2,2,3,3,1,1, how can i do it with select query or c# entity framework?

• Erik Reasonable Rates Darling: You can use a CASE expression in the ORDER BY, but this gets incredibly unwieldy after a few values.
• markp-fuso: you need to provide more details on how you come up with those other sort orders (2,2,1,1,3,3 and 2,2,3,3,1,1); do you have other columns in the table that determine the ordering, or are you just making up the sort order as you go along? if there's some 'logic' to your sort ordering then we would need said 'logic'
• OnePage: i haven't other column for sort. Actually t1 generated 3 tables with inner join query but i want sort of y columns with 2,2,1,1,3,3 or 2,2,1,1,3,3(both of result doesn't matter, i want returned list start with 2)
• ypercubeᵀᴹ: If the 2,2,3,3,1,1 means "I want from 2 ascending up to the max value, then from 0 ascending" ORDER BY CASE WHEN y >= 2 THEN 0 ELSE 1 END, y ; and (the opposite) ORDER BY CASE WHEN y <= 2 THEN 0 ELSE 1 END, y DESC ;

Answer 1

For a simple solution based on just the one 'y=2' value (as per comments), the suggested order by case ... construct should suffice.

However, if the requirement is to be able to sort by a varying number of 'y' values, or perhaps order by a combination of columns ...

---

One idea that comes to mind ... use a table value constructor (TVC) to build an 'inline' table of your desired sort order; we'll then join this TVC back to your original data set to provide the desired ordering sequence.

We'll start by creating our table and populating with some sample data:

drop table if exists t1;
create table t1
(id int
,x varchar(30)
,y int);
insert into t1 (id,x,y) values
(1,'a',1),
(2,'b',3),
(3,'c',2),
(4,'d',2),
(5,'e',1),
(6,'f',3);

For our TVC we'll generate a single-column derived table called dt(y) that includes the y values listed in their desired sort order. We'll add the row_number() function to provide the column that we'll actually order by. Finally, we'll wrap all of this in a common table expression (CTE) for ease of reference.

-- order by 'y' values 2, 1, 3:
with myorder as
(select row_number() over (order by (select 1)) orderid,
 y
 from (values (2),(1),(3) ) as dt(y) -- desired sort order is 2, 1, 3
)
select *
from myorder
order by orderid;
 orderid | y
 ------- | --
 1 | 2
 2 | 1
 3 | 3
-- order by 'y' values 2, 3, 1:
with myorder as
(select row_number() over (order by (select 1)) orderid,
 y
 from (values (2),(3),(1) ) as dt(y) -- desired sort order is 2, 3, 1
)
select *
from myorder
order by orderid;
 orderid | y
 ------- | --
 1 | 2
 2 | 3
 3 | 1

We can now join our TVC with the original data set (table t1 in this case) to generate the desired sort order:

-- sort solely on the TVC's 'y' column:
with myorder as
(select row_number() over (order by (select 1)) orderid,
 y
 from (values (2),(3),(1) ) as dt(y)
)
select t1.id,
 t1.x,
 t1.y
from t1
join myorder mo
on mo.y = t1.y
order by mo.orderid;
 id | x | y
 -- | -- | --
 3 | c | 2
 4 | d | 2
 2 | b | 3
 6 | f | 3
 1 | a | 1
 5 | e | 1
-- sort by TVC's 'y' column (ascending order), then by t1's 'x' column (descending order):
with myorder as
(select row_number() over (order by (select 1)) orderid,
 y
 from (values (2),(3),(1) ) as dt(y)
)
select t1.id,
 t1.x,
 t1.y
from t1
join myorder mo
on mo.y = t1.y
order by mo.orderid, 
 t1.x desc
 id | x | y
 -- | -- | --
 4 | d | 2
 3 | c | 2
 6 | f | 3
 2 | b | 3
 5 | e | 1
 1 | a | 1

The key item to remember is to make sure the TVC's records are listed in an order that matches the desired sort order.

---

One potential issue is that if you don't know how many 'y' values you have, or perhaps you only need to sort by a handful of known 'y' values (and don't care about ordering of other 'y' values), you could:

• limit the TVC to just those 'y' values you're interested in and ...
• join the main data set via an outer join to the TVC and ...
• then default the the missing TVC's 'orderid' columns to some sufficiently large number to ensure their location in the sort order does not affect the 'y' values of interest

For example, assume we want 'y=2' values to be listed first, and we don't care about the rest of the 'y' values:

with myorder as
(select row_number() over (order by (select 1)) orderid,
 y
 from (values (2) ) as dt(y) -- only interested in 'y=2' records
)
select t1.id,
 t1.x,
 t1.y
from t1 -- left join w/ TVC/CTE
left
join myorder mo
on mo.y = t1.y
order by isnull(mo.orderid, 100), -- default missing TVC.orderid's to 100
 t1.x desc
 id | x | y
 -- | -- | --
 4 | d | 2 -- 'y=2' records show up
 3 | c | 2 -- first in our output
 6 | f | 3 -- while we let the database engine
 5 | e | 1 -- determine the order of the rest
 2 | b | 3 -- of the result set; granted, for this
 1 | a | 1 -- example we also had 't1.x desc'

Obviously you could still add other columns to the 'order by' clause to affect the final ordering.

Here's a dbfiddle for all of the above.

---

While the example queries (above) are based on hard coding the sort order for the 'y' column, this method could be expanded to allow for hard coding the sort order for any combination of columns.

Let's say for example you want a hard coded sort order based on 'y=2' and 'x in ('a','b','c')', the CTE/TVC would look like:

with myorder as
(select row_number() over (order by (select 1)) orderid,
 x
 y
 from (values (2,'a'),(2,'b'),(2,'c') ) as dt(x,y)
)

The main query could then be (outer) joined to myorder on the x and y columns:

with myorder as(...)
select ...
from t1
left
join myorder mo
on mo.x = t1.x
and mo.y = t1.y
order by isnull(mo.orderid, 100);

Again, if you don't need the flexibility then the more simple order by case ... construct should be sufficient.

Answer 2

You could add sort columns to the table for each use case. You need to have something logical in your table to sort on and I don't see how you could get 2,2,1,1,3,3 or 2,2,3,3,1,1 with your current columns.