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 used to sort results returned by the SELECT statement. If we apply for the ORDER BY clause on a particular column, which is the VARCHAR data type, it will sort this according to the dictionary. But if we want to sort this column from the specific value, we can customize the ORDER BY clause.
See the below 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 the query will be as bellow

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

DROP TABLE #TMP




No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...