Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Sort certain values last

From Wiki

Jump to: navigation, search

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

  1. DECLARE @Temp TABLE(Col CHAR(1))
  2.     INSERT INTO @Temp VALUES('A')
  3.     INSERT INTO @Temp VALUES('B')
  4.     INSERT INTO @Temp VALUES('C')
  5.     INSERT INTO @Temp VALUES('E')
  6.     INSERT INTO @Temp VALUES('D')
  7.     INSERT INTO @Temp VALUES('X')
  8.     INSERT INTO @Temp VALUES('X')
  9.     INSERT INTO @Temp VALUES('Y')
  10.     INSERT INTO @Temp VALUES('Z')
  11.     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

  1. SELECT *
  2.     FROM @Temp
  3.     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

  1. SELECT *
  2.     FROM @Temp
  3.     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

136 Rating: 1.0/5 (3 votes cast)