1

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?

  • 2
    You can use a CASE expression in the ORDER BY, but this gets incredibly unwieldy after a few values. – Erik Darling Oct 6 '17 at 13:48
  • 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' – markp Oct 6 '17 at 14:20
  • 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) – OnePage Oct 6 '17 at 14:34
  • 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 ; – ypercubeᵀᴹ Oct 6 '17 at 15:05
3

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.