How to make this query in T-SQL
I have a table with 3 columns AB C.
I want to select * from this table, but ordered by a specific order of column A.
In other words, let's say column A contains "stack", "over", "stream".
I want to select * from this table and order by column A in that particular order: "stack", "flow", "above", which is neither upstream nor downstream.
Is it possible?
source to share
You can use the 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
Learn more about Defining a Custom Sort Order .
source to share
Several solutions:
Create another table with sort order, join column A to the new table (it will be something like TERM like STRING, SORTORDER as INT). Since things always change, this avoids hard coding, and this is the solution I would recommend for real world use.
If you don't want the flexibility to add new terms and orders, just use the CASE statement to convert each term to a number:
CASE A WHEN 'stack' THEN 1 WHEN 'over' THEN 2 WHEN 'flow' THEN 3 END
and use it in your ORDER BY.
source to share
If you have many custom order items, you can add those items to the table and give them a value. Join 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 only have 3 items as suggested in your question, you can use case in order ...
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
source to share