CODEWITHSUNDEEP
sqlt-sql
mardok
mardok

Reputation: 2415

Custom ordering in TSQL

I need to implement custom ordering for my table. For example I have a table called "TestTest" with values: a, d01, d04, d02, b . I need to select data and order them so that values with "d" will be first and rest will be sorted alphanumerical. So the result would be d01,d02,d03,a,b

Script to create and insert data:

CREATE TABLE TestTest(
Name varchar(200)
)
DELETE FROM TestTest

INSERT INTO TestTest( Name )
VALUES( 'a' )

INSERT INTO TestTest( Name )
VALUES( 'd01');

INSERT INTO TestTest( Name )
VALUES( 'd04');

INSERT INTO TestTest( Name )
VALUES( 'd02');

INSERT INTO TestTest( Name )
VALUES( 'b' );

Thx for any help ;)

Upvotes: 0

Views: 98

Answers (3)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Select *
From TestTest
Order By CASE WHEN LEFT(Name,1)='d' THEN 1 ELSE 2 END,Name

SQL Fiddle Demo

Upvotes: 4

vhadalgi
vhadalgi

Reputation: 7189

select * from TestTest order by case when Name='d01' then 1
                                            when Name='d02' then 2
                                            when Name ='d04' then 3
                                            end  desc

Upvotes: 1

PeterRing
PeterRing

Reputation: 1797

Quick soution:

Select * 
from TestTest
order by case when Name like 'd%' then 'aaaaa'+Name else Name end

Upvotes: 1

Related Questions