CODEWITHSUNDEEP
sqlsql-server-2008sql-order-by
Matt
Matt

Reputation: 273

SQL Specify the order of ORDER BY result set

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

Upvotes: 0

Views: 720

Answers (2)

anon
anon

Reputation:

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

Upvotes: 4

sazh
sazh

Reputation: 1832

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

Upvotes: -1

Related Questions