Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

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.3/5 (6 votes cast)