CODEWITHSUNDEEP
sqlsql-server
user3670046
user3670046

Reputation: 13

Reordering output to predefined sequence

I am trying to get output from a table sorted in a predefined sequence of 5 alphabet. i.e. L > C > E > O > A by using order by I cant get the desired result. I am using SQL server db.

Can any one please suggest me if I can define a sequence inside a query ? SO that I get my result in L > C > E > O > A.

Thanks in Advance.

Upvotes: 1

Views: 45

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

If you want to use those sorting criteria for two or more queries then you can create a table for this:

CREATE TABLE dbo.CustomSort (
    Value VARCHAR(10) PRIMARY KEY,
    SortOrder INT NOT NULL
);
GO
INSERT INTO dbo.CustomSort (Value, SortOrder) VALUES ('L', 1);
INSERT INTO dbo.CustomSort (Value, SortOrder) VALUES ('C', 2);
INSERT INTO dbo.CustomSort (Value, SortOrder) VALUES ('E', 3);
INSERT INTO dbo.CustomSort (Value, SortOrder) VALUES ('O', 4);
INSERT INTO dbo.CustomSort (Value, SortOrder) VALUES ('A', 5);
GO

and then you can join the source table (x in this example) with dbo.CustomSort table thus: 

SELECT  x.Col1
FROM    
(
    SELECT 'E' UNION ALL 
    SELECT 'C' UNION ALL 
    SELECT 'O'
) x(Col1) INNER JOIN dbo.CustomSort cs ON x.Col1 = cs.Value 
ORDER BY cs.SortOrder
/*
Col1
----
C
E
O
*/

I you update the dbo.CustomSort table then all queries will use the new sorting criteria.

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select * from your_table
order by case when some_column = 'L' then 1
              when some_column = 'C' then 2
              when some_column = 'E' then 3
              when some_column = 'O' then 4
              when some_column = 'A' then 5
         end desc

Upvotes: 4

Related Questions