ordering by alpha numeric string in SQL-CodePudding

I'm working with a query that looks like this:

SELECT
  CASE 
    WHEN num BETWEEN 1 AND 5 THEN '1-5'
    WHEN num BETWEEN 6 AND 10 THEN '6-10'
    WHEN num BETWEEN 11 AND 20 THEN '11-20'
    WHEN num > 20 THEN '20 '
END AS bucket,
COUNT(1)
FROM data
GROUP BY 1

...

I'd like to order the results by the different values of bucket so the results look something like this

bucket	count(1)
1-5	10
6-10	11
11-20	17
20	8

Since bucket is a string, simply sorting by that column doesn't do the trick.

Are there any string operations in SQL that can help me? For reasons I won't get into the CASE statements are the easiest way for me to get results in the format I need, so I'd rather deal with a bunch of string operations to get it in the order I want than change the structure of the query

CodePudding user response：

My approach uses integer lower-bounds for the buckets (as integers are nicer to deal with than strings), and only generates the string bucket names at the very end, after grouping.

WITH histogramData AS (
    SELECT
        CASE
            WHEN num <  1 THEN NULL
            WHEN num <  6 THEN 1
            WHEN num < 11 THEN 6
            WHEN num < 21 THEN 11 ELSE 21
        END AS Bucket,
        data.*
    FROM
        data
),
grouped AS (
    SELECT
        h.Bucket,
        COUNT(*) AS [Count]
    FROM
        histogramData AS h
    GROUP BY
        h.Bucket
)
SELECT
    CASE ISNULL( g.Bucket, 0 )
        WHEN  0 THEN NULL,
        WHEN  1 THEN '1-5'
        WHEN  6 THEN '6-10'
        WHEN 11 THEN '11-20' ELSE '21 '
    END AS [Bucket Name],
    g.[Count]
FROM
    grouped
ORDER BY
    g.Bucket

CodePudding user response：

SELECT
  CASE 
    WHEN num BETWEEN 1 AND 5 THEN '01-05'
    WHEN num BETWEEN 6 AND 10 THEN '06-10'
    WHEN num BETWEEN 11 AND 20 THEN '11-20'
    WHEN num > 20 THEN '20 '
END AS bucket,
COUNT(1)
FROM data
GROUP BY 1