Oct 16, 2015

Ordering SQL query by specific field values in SQL Server

I am doing custom sorting in order by clause in SQL Server. As we know ORDER BY clause use to sort results returned by SELECT statement. If we apply ORDER BY clause on particular column which is VARCHAR data type it will sort this according dictionary. but if we want to sort this column from particular value the we can customize ORDER BY clause.
See bellow example.


CREATE TABLE #TMP
(
       ID INT IDENTITY(1,1),
       NAME VARCHAR(100),
)

INSERT INTO #TMP
SELECT 'ASHISH'
UNION ALL
SELECT 'CHANDAN'
UNION ALL
SELECT 'DILIP'
UNION ALL
SELECT 'ESHA'
UNION ALL
SELECT 'FIZA'
UNION ALL
SELECT 'MAHESH'
UNION ALL
SELECT 'VIPUL'
UNION ALL
SELECT 'ANIL'

-- I want to sort NAME column from value 'DILIP' then query will be as bellow

SELECT * FROM #TMP ORDER BY CASE WHEN NAME='DILIP' THEN '1' ELSE NAME END ASC

DROP TABLE #TMP