Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Another Interesting Sort

In October of 2011, I shared an example of a peculiar set of sort requirements.  Today, I am going to share another similar set of requirements.  Based on prior experience, when I saw this request on a sort order, I was sure I could find a simpler solution.  Today, I will share the requirements and solution with you.

Requirements

Given a set of characters, you must be able to sort according to the following:

  1. !
  2. $
  3. ?
  4. @
  5. ^
  6. {
  7. >
  8. ASCII values

Setup

To demonstrate the requirements and solution, let’s create a temp table and populate it with some values like those in the requirements.

CREATE TABLE #Chars
(
	MyChar CHAR(1)
)
 
INSERT INTO #Chars (MyChar)
VALUES
  ('!'), ('"'), ('$'), ('?'), ('@'), ('^')
, ('{'), ('>'), ('1'), ('2'), ('3'), ('4')
, ('5'), ('6'), ('7'), ('8'), ('9'), ('A')
, ('B'), ('C'), ('D')

Solution

SELECT *
	FROM #Chars
	ORDER BY MyChar COLLATE SQL_Latin1_General_Cp1251_CS_AS

Sometimes the simplest solution requires a lot of testing.  When I came across the requirements, I thought it could be solved via a collation.  The problem was that I did not know which collation.  I had to test a few collations to find the collation that would create the correct result set.  If you are interested in learning about other collations, you can read this article.

Comments

Leave a comment on the original post [jasonbrimhall.info, opens in a new window]

Loading comments...