Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Sort certain values last
From Wiki
Let's say you have a result set but you want to show certain values first and then all the other values. How can you do that? You can use a CASE statement. Let's take a look how that works. First create this table
- DECLARE @Temp TABLE(Col CHAR(1))
- INSERT INTO @Temp VALUES('A')
- INSERT INTO @Temp VALUES('B')
- INSERT INTO @Temp VALUES('C')
- INSERT INTO @Temp VALUES('E')
- INSERT INTO @Temp VALUES('D')
- INSERT INTO @Temp VALUES('X')
- INSERT INTO @Temp VALUES('X')
- INSERT INTO @Temp VALUES('Y')
- INSERT INTO @Temp VALUES('Z')
- INSERT INTO @Temp VALUES('Z')
Let's say that you want to show all Zs first and then all the other values ascending. This is how you do that
- SELECT *
- FROM @Temp
- ORDER BY CASE WHEN Col ='Z' THEN 0 ELSE 1 END, Col
Output
| Z |
| Z |
| A |
| B |
| C |
| D |
| E |
| X |
| X |
| Y |
You can also use between with CASE, for example first show me all the values between X and Z, then the rest
- SELECT *
- FROM @Temp
- ORDER BY CASE WHEN Col between 'X' AND 'Z' THEN 0 ELSE 1 END, Col
Output
| X |
| X |
| Y |
| Z |
| Z |
| A |
| B |
| C |
| D |
| E |
Contributed by: --SQLDenis 02:42, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Ranking, Max etc
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.