ORDER BY the Items Within a Case Statement

Question

Is there another way I can sort the results by the Age Group appropriately? As a work-around, I placed a character before each age group to display in chronological order - but if the letter is not there, then it does not display in the order I am expecting. Here is the T-SQL:

WITH AgeData
AS ( SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - CASE WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
THEN 1
ELSE 0
END AS [Age]
FROM   dbo.Customers ) ,
 GroupAge
AS ( SELECT [Age] ,
        CASE WHEN AGE < 4 THEN 'a0 - 3'
             WHEN AGE BETWEEN 4 AND 8 THEN 'b4 - 8'
             WHEN AGE BETWEEN 9 AND 12 THEN 'c9 - 12'
             WHEN AGE BETWEEN 13 AND 17 THEN 'd13 - 17'
             WHEN AGE BETWEEN 18 AND 22 THEN 'e18 - 22'
             WHEN AGE BETWEEN 23 AND 26 THEN 'f23 - 26'
             WHEN AGE BETWEEN 27 AND 33 THEN 'g27 - 33'
             WHEN AGE BETWEEN 34 AND 40 THEN 'h34 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN 'i41 - 50'
             WHEN AGE BETWEEN 51 AND 60 THEN 'j51 - 60'
             WHEN AGE BETWEEN 61 AND 65 THEN 'k61 - 65'
             WHEN AGE BETWEEN 66 AND 74 THEN 'l66 - 74'
             WHEN AGE > 75 THEN 'm75+'
             ELSE 'nInvalid Birthdate'
        END AS [AgeGroups]
 FROM   AgeData
)
SELECT   COUNT(*) AS [AgeGroupCount] ,
     [AgeGroups]
FROM     GroupAge
GROUP BY GroupAge.[AgeGroups]
ORDER BY GroupAge.[AgeGroups];

Without the character such as 'a', 'b', 'c', etc... my result set looks like:

If possible, I'd like to sort correctly without the work-around of using a letter.

separate the age part after the dash, convert it to integer and sort by this. (Consider a special handling for 75+) — Alkis Giamalis, May 19 '17 at 07:04

TriV · Accepted Answer · 2017-05-19T06:16:28.150

You could add one more column to order item

WITH AgeData
AS ( 
SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - 
      CASE 
            WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
            THEN 1
      ELSE 0
      END AS [Age]
FROM   dbo.Customers ) ,
GroupAge
AS (       SELECT [Age] ,
      CASE WHEN AGE < 4 THEN '0 - 3'
          WHEN AGE BETWEEN 4 AND 8 THEN '4 - 8'
          WHEN AGE BETWEEN 9 AND 12 THEN '9 - 12'
          WHEN AGE BETWEEN 13 AND 17 THEN '13 - 17'
          WHEN AGE BETWEEN 18 AND 22 THEN '18 - 22'
          WHEN AGE BETWEEN 23 AND 26 THEN '23 - 26'
          WHEN AGE BETWEEN 27 AND 33 THEN '27 - 33'
          WHEN AGE BETWEEN 34 AND 40 THEN '34 - 40'
          WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
          WHEN AGE BETWEEN 51 AND 60 THEN '51 - 60'
          WHEN AGE BETWEEN 61 AND 65 THEN '61 - 65'
          WHEN AGE BETWEEN 66 AND 74 THEN 'l66 - 74'
          WHEN AGE > 75 THEN 'm75+'
          ELSE 'nInvalid Birthdate'
      END AS [AgeGroups],
      CASE WHEN AGE < 4 THEN 1
          WHEN AGE BETWEEN 4 AND 8 THEN 2
          WHEN AGE BETWEEN 9 AND 12 THEN 3
          WHEN AGE BETWEEN 13 AND 17 THEN 4
          WHEN AGE BETWEEN 18 AND 22 THEN 5
          WHEN AGE BETWEEN 23 AND 26 THEN 6
          WHEN AGE BETWEEN 27 AND 33 THEN 7
          WHEN AGE BETWEEN 34 AND 40 THEN 8
          WHEN AGE BETWEEN 41 AND 50 THEN 9
          WHEN AGE BETWEEN 51 AND 60 THEN 10
          WHEN AGE BETWEEN 61 AND 65 THEN 11
          WHEN AGE BETWEEN 66 AND 74 THEN 12
          WHEN AGE > 75 THEN 13
          ELSE 14
      END AS [AgeGroupId]
FROM   AgeData
)
SELECT   COUNT(*) AS [AgeGroupCount] ,
      [AgeGroups]
FROM     GroupAge
GROUP BY GroupAge.[AgeGroups],[AgeGroupId]
ORDER BY GroupAge.[AgeGroupId]

Another solution: Use a temp table that contains group information

DECLARE @GroupAge AS TABLE
(
   GroupID int,
   StartAge int,
   EndAge int,
   GroupName AS CONCAT(StartAge, '-', EndAge)
)

INSERT INTO @GroupAge
(
    GroupID,
    StartAge,
    EndAge
)
VALUES (1,0,3) -- insert all groups you need

;WITH AgeData
AS ( 
SELECT DATEDIFF(YEAR, birthDate, GETDATE()) - 
      CASE 
            WHEN GETDATE() < DATEADD(YEAR , DATEDIFF(YEAR, birthDate, GETDATE()), birthDate )
            THEN 1
      ELSE 0
      END AS [Age]
FROM   dbo.Customers ) 
SELECT   COUNT(*) AS [AgeGroupCount] ,
         ga.GroupName
FROM     AgeData a
INNER JOIN @GroupAge ga ON ( a.Age BETWEEN ga.StartAge AND ga.EndAge)
GROUP BY ga.GroupID, ga.GroupName
Order By ga.GroupID

I was going to suggest this, but it seems really ugly. Maybe there is a better way. — Tim Biegeleisen, May 19 '17 at 05:26
TriV - I liked the second approach but I don't see a way to handle the 75+ group or the DOBs that are invalid. The first approach does work well though. — MISNole, May 19 '17 at 12:48
TriV - Nevermind - I simply entered 250 as an EndAge and it works - I can always rename the column header in SSRS - but this assumes we don't see a human live past 250 years. — MISNole, May 19 '17 at 13:39
Haha maximum value for interger is more than 2000000000. You have many choices.... — TriV, May 19 '17 at 14:08

score 0 · Answer 2 · answered May 19 '17 at 06:42

In your ORDER BY clause, just add the following line:

IIF([AgeGroups] = 'Invalid Birthdate', 999, CAST(LEFT([AgeGroups], CHARINDEX('-', REPLACE([AgeGroups], '+', '-'))- 1) AS INT))

This is full working example:

DECLARE @DataSource TABLE
(
    [AgeGroups] VARCHAR(18)
);

INSERT INTO @DataSource
VALUES ('0-3')
      ,('13-17')
      ,('18-22')
      ,('23-26')
      ,('27-33')
      ,('34-40')
      ,('4-8')
      ,('41-50')
      ,('51-60')
      ,('61-65')
      ,('66-74')
      ,('75+')
      ,('9-12')
      ,('Invalid Birthdate');

SELECT *
      ,IIF([AgeGroups] = 'Invalid Birthdate', 999, CAST(LEFT([AgeGroups], CHARINDEX('-', REPLACE([AgeGroups], '+', '-'))- 1) AS INT))
FROM @DataSource
ORDER BY IIF
         (
            [AgeGroups] = 'Invalid Birthdate'
           ,999
           ,CAST(LEFT([AgeGroups], CHARINDEX('-', REPLACE([AgeGroups], '+', '-'))- 1) AS INT)
         );

The idea is to get the start number for each range, covert it to number and sort by it. We just need to add extra check for the Invalid Birthdate string and replace the + with - for the 75+ value.

ORDER BY the Items Within a Case Statement

2 Answers2