Sql Server : ORDER BY clause beauty and complexity (Tidbit 3)
- Posted by Sqltimes
- On January 10, 2015
- 0 Comments
A few days ago, there was an interesting requirement. We have a query that needs to bring data from a set of tables in a particular order that did not seem straight forward. After some head scratching and banging on the desk, with no avail, my colleague Dylan rescued the quickly dying hope.
Requirement
We have a query that returns necessary data set to be populated on the webpage. But the resultant data set needs to be broken into two logical groups. The first group, should only contain data where a particular column is NULL or empty. Second group has non-NULL data. First group is always displayed at the top of the screen followed by Group 2. But both the groups need to be ORDERed by a different column.
Let’s simplify it further. Entire data set, needs to be ORDERed BY two columns, let’s say, Col8. But all records will NULL value for Col2 needs to be on the top (still ordered by Col8).
So, it is not a simple ORDER BY Col2 ASC, Col8 ASC. As this would result in second group being ordered by Col2 first then Col8. This is incorrect. So we need a way to combine the data set into two groups, with NULL and without NULL; and then order them by Col9.
Let’s take sample data set.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
---- Sample table to demonstrate--CREATE TABLE dbo.test1 ( ID INT NOT NULL IDENTITY(1,1) , ExtID VARCHAR(30) , Descrip VARCHAR(50) , Name VARCHAR(20))GOINSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('143', 'Apple', 'Joe')INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('0140', 'Adam', 'Jim')INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('123', 'Cucumber', 'Tim')INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('133', 'Banana', 'Kim')INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('170', NULL, 'Sim')INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('160', '', 'Rim')INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('150', NULL, 'Lin')GO |
Correct Order of Data
Consider this query:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
---- Beauty of ORDER BY clause--SELECT ExtID , ISNULL(Descrip,'') AS Descrip , NameFROM dbo.test1ORDER BY CASE WHEN Descrip IS NULL OR Descrip = '' THEN NULLIF(Descrip,'') ELSE CAST(ExtID AS INT) END , CAST(ExtID AS INT)GO |
Usually, ORDER BY clause is one of the last clauses to be processed (in a SELECT statement). So, once a data set (internal virtual table) is created by FROM clause and WHERE clause; ORDER BY is applied on that internal virtual table to sort the dataset in correct format.
Using complicated ORDER BY statements you could order the data as you need. The above query is similar to having a new column added to SELECT clause with the same CASE statement logic as in ORDER BY clause. See below:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
---- Beauty of ORDER BY clause--SELECT CASE WHEN Descrip IS NULL OR Descrip = '' THEN NULLIF(Descrip,'') ELSE CAST(ExtID AS INT) END AS [NewDescrip] , ExtID , ISNULL(Descrip,'') AS Descrip , NameFROM dbo.test1ORDER BY [NewDescrip] , CAST(ExtID AS INT)GO |
ORDER BY Clause with SELECT column
So, if you want to order a data set in a particular way, first try to add columns to SELECT clause with that logic (to make it easier to visualize), then use the same logic in ORDER BY clause.
This is very interesting !! Thanks Dylan. Not sure if I never know this feature or completely forgot it after used it before. Either way, good to learn something new again.
_Sqltimes
0 Comments