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
