Custom sorting using two different columns sql

Question

In my table, I have these two columns called year and season that i'd like to sort by. Some example of their values might be

----------------------------
| id | etc | year | season |
| 0  | ... | 2016 | FALL   |
| 1  | ... | 2015 | SPRING |
| 2  | ... | 2015 | FALL   |
| 3  | ... | 2016 | SPRING |
----------------------------

How would I go about performing a select where I get the results as such?

| 1 | ... | 2015 | SPRING |
| 2 | ... | 2015 | FALL   |
| 3 | ... | 2016 | SPRING |
| 0 | ... | 2016 | FALL   |

The easy part would be ORDER BY table.year ASC, but how do I manage the seasons now? Thanks for any tips!

Answer 1

If you want to order by all four seasons, starting with Spring, extend your CASE statement:

ORDER BY CASE season
    WHEN 'spring' then 1
    WHEN 'summer' then 2
    WHEN 'fall' then 3
    WHEN 'autumn' then 3
    WHEN 'winter then 4
    ELSE 0   -- Default if an incorrect value is entered. Could be 5
END

Alternately, to handle all possible cases, you might want to build a table with the season name and a sort order. Say, for example, some of your data was in german. You could have a table - SeasonSort - with the fields SeasonName and SortOrder. Then add data:

CREATE TABLE SeasonSort (SeasonName nvarchar(32), SortOrder tinyint)
INSERT INTO SeasonSort (SeasonName, SortOrder)
VALUES 
    ('spring', 1),
    ('frühling', 1),
    ('fruhling', 1),   -- Anglicized version of German name
    ('summer', 2),
    ('sommer', 2),
    ('fall', 3),
    ('autumn', 3),
    ('herbst', 3),
    ('winter', 4)   -- same in English and German

Then your query would become:

SELECT t.*
FROM MyTable t
LEFT JOIN seasonSort ss
    ON t.season = ss.SeasonName
ORDER BY t.Year,
    isnull(ss.SortOrder, 0)

Answer 2

You can do this:

SELECT *
FROM yourtable
ORDER BY year, CASE WHEN season = 'spring' THEN 0 ELSE 1 END;

---

If you want to do the same for the other two seasons, you can do the same using CASE, but it will be much easier and more readable to use a table something like this:

SELECT t1.*
FROM yourtable AS t1
INNER JOIN
(
   SELECT 'spring' AS season, 0 AS sortorder
   UNION
   SELECT 'Fall' AS season, 1 AS sortorder
   UNION
   SELECT 'Winter' AS season, 2 AS sortorder
   UNION
   SELECT 'summer' AS season, 3 AS sortorder
) AS t2
ORDER BY t1.year, t2.season;