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

Reputation: 273

SQL ORDER BY Statement logic issue

I have a resultset of Stuyear, Name, Included and %3+ Levels and I'm trying to ORDER BY so that the results set displays as English, Maths and then all other subjects in alphabetical order. Each subject is in turn sorted into year group order.

This is my code:

ORDER BY CASE NAME 
      WHEN 'English' THEN 0 
      WHEN 'Mathematics' THEN 1 
            ELSE 2 END, 
Stuyear DESC

Which is resulting in this:

enter image description here

So the initial part of the sort is working as expected, but then the subjects after English and Maths don't sort as I require them to. eg:

Stuyear    name
11         English
10         English
9          English
11         Mathematics
10         Mathematics
9          Mathematics
11         Art & Design
10         Art & Design
9          Art & Design
...

Upvotes: 2

Views: 143

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

It should be:

ORDER BY CASE NAME 
  WHEN 'English' THEN 0 
  WHEN 'Mathematics' THEN 1 
        ELSE 2 END, 
  NAME,
  Stuyear DESC

In your case you were telling it to `ORDER BY' name only in English and Mathematics, and after that by Stuyear.

All the other "Names" would have 2 for that ORDER BY clause, and the only thing you were telling it was to order by Stuyear after. That way it wouldn't know how to sort those.

Putting the Name column after the case it will make it do what you expect.

Upvotes: 8

Related Questions