How to do this query in T-SQL

Question

I have table with 3 columns A B C.

I want to select * from this table, but ordered by a specific ordering of column A.

In other words, lets' say column A contains "stack", "over", "flow".

I want to select * from this table, and order by column A in this specific ordering: "stack", "flow", "over" - which is neither ascending nor descending.

Is it possible?

Answer 1

You can use a CASE statement in the ORDER BY clause. For example ...

SELECT *
FROM Table
ORDER BY
   CASE A
      WHEN 'stack' THEN 1
      WHEN 'over' THEN 2
      WHEN 'flow' THEN 3
      ELSE NULL
   END

Check out Defining a Custom Sort Order for more details.

Answer 2

A couple of solutions:

Create another table with your sort order, join on Column A to the new table (which would be something like TERM as STRING, SORTORDER as INT). Because things always change, this avoids hard coding anything and is the solution I would recommend for real world use.

If you don't want the flexibility of adding new terms and orders, just use a CASE statement to transform each term into an number:

CASE A WHEN 'stack' THEN 1 WHEN 'over' THEN 2 WHEN 'flow' THEN 3 END

and use it in your ORDER BY.

Answer 3

If you have alot of elements with custom ordering, you could add those elements to a table and give them a value. Join with the table and each column can have a custom order value.

   select 
      main.a,
      main.b,
      main.c 
   from dbo.tblMain main
   left join tblOrder rank on rank.a = main.a 
   order by rank.OrderValue

If you have only 3 elements as suggested in your question, you could use a case in the order by...

   select 
      * 
   from dbo.tblMain 
   order by case 
      when a='stack' then 1 
      when a='flow' then 2 
      when a='over' then 3 
      else 4
   end