SQL Specify the order of ORDER BY result set

Question

I have an ORDER BY clause which order a result set of grouped values.

What I would like the ORDER By to do is result in the No KS2 row being at the top.

The values are conditionally populated from three different columns, but the values are from the same set across all three columns.

The values are null, a string of length zero, 1c, 1b, 1a, 2c, 2b, 2a, 3c.... 5a, 6c, 6b, 6a

Currently my ORDER BY clause brings out the values by ordering baased on the left of the value and the right of the values DESC.

And example result set would be:

2a
3c
3b
4c
4b
4a
5c
5b
No KS2

Here is an example of what I would like:

No KS2
2a
3c
3b
4c
4b
4a
5c
5b

The code I have currently is here:

ORDER BY
LEFT(
CASE Name
    WHEN 'English' THEN
        CASE WHEN [Ks2en]=NULL OR [Ks2en]='' THEN
            'No KS2'
        ELSE
            [Ks2en]
        END
    WHEN 'Mathematics' THEN
        CASE WHEN [Ks2ma]=NULL OR [Ks2ma]='' THEN
            'No KS2'
        ELSE
            [Ks2ma]
        END
    ELSE
        CASE WHEN [Ks2av]=NULL OR [Ks2av]='' THEN
            'No KS2'
        ELSE
            [Ks2av]
        END
    END,1),
RIGHT(
CASE Name
    WHEN 'English' THEN
        CASE WHEN [Ks2en]=NULL OR [Ks2en]='' THEN
            'No KS2'
        ELSE
            [Ks2en]
        END
    WHEN 'Mathematics' THEN
        CASE WHEN [Ks2ma]=NULL OR [Ks2ma]='' THEN
            'No KS2'
        ELSE
            [Ks2ma]
        END
    ELSE
        CASE WHEN [Ks2av]=NULL OR [Ks2av]='' THEN
            'No KS2'
        ELSE
            [Ks2av]
        END
    END,1) DESC

http://stackoverflow.com/questions/1250156/how-do-i-return-rows-with-a-specific-value-first -> this should give you some idea — usha, Oct 02 '13 at 16:54
Apologies guys, got out of the habit of starting with the question rather than where it comes as I write my post. You'd have found it about half way down, but appreciate by then you may have fallen asleep. Amended answer to accommodate. — Matt, Oct 02 '13 at 17:03
@dognose it makes more sense to the end user to see 'No KS2' rather than just an empty row. The values I talk about are grades that students get at the end of their primary school, the curriculum for primary school is known as Key Stage 2. — Matt, Oct 02 '13 at 17:07

Aaron Bertrand · Accepted Answer · 2013-10-02T17:27:18.227

4

Put your query with its case output inside a subquery, then on the outer query check it so you can order it first (using a constant). This prevents you from having to repeat that messy CASE condition in the ORDER BY (I presume the same or similar also exists in the SELECT list). This doesn't map to your code exactly (particularly since we don't have all of your code), but should give an idea:

SELECT Ks2en /* , other columns */
FROM
(
  SELECT Ks2en = CASE WHEN Ks2en = 'x' THEN 'No KS2' ELSE Ks2en END
    /* , other columns */
  FROM ...your query...
) AS x
ORDER BY CASE WHEN Ks2en = 'No KS2' THEN 'a' ELSE 'b' END, Ks2en;

http://sqlfiddle.com/#!3/128df/2

edited Oct 02 '13 at 17:27

answered Oct 02 '13 at 17:17

Aaron Bertrand

272,866
37
466
490

Yes and the GROUP BY. – Matt Oct 02 '13 at 17:19
@Matt *what* GROUP BY? – Aaron Bertrand Oct 02 '13 at 17:20
Its not in my example, but I refer to the result set being grouped in the question. I'm happy with your answer, just need a second to try it out. – Matt Oct 02 '13 at 17:46

score -1 · Answer 2 · answered Oct 02 '13 at 16:56

-1

In your order by, can you do the following?

ORDER BY
    CASE WHEN [Ks2ma]=NULL OR [Ks2ma]='' THEN -1 ELSE [Ks2en] END

i.e. always force the 'No KS2' to be a value that you know will sort higher than the rest (based on your data set). This only needs to be done in the ORDER BY

answered Oct 02 '13 at 16:56

sazh

1,792
15
20

3

(1) `=NULL` should be `IS NULL`. (2) the -1 will lead to a conversion error, since it will try to convert `Ks2en` to an `INT`. A `CASE` expression must collapse to compatible types according to data type precedence. – Aaron Bertrand Oct 02 '13 at 17:18
oops - you're right! I just copied his case statement without thinking. – sazh Oct 03 '13 at 01:04

SQL Specify the order of ORDER BY result set

2 Answers2