SQL Datepart (week), ORDER BY year?

Question

I use this following query here to get my data and show the value weekly, however, when there is a new year and the result will show week 50, 51, 52 of 2016 and week 1, 2, 3 of 2017 for example, it will order by week number resulting in 1,2,3,50,51,52 which means that the 2017 results appear before 2016, how do I change this?

SELECT 
    DATEPART(wk, date) AS number,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY DATEPART(wk, date)

Answer 1

Perhaps not the most elegant solution, but you could just include the year in the field you group by:

SELECT 
    CONVERT(VARCHAR,DATEPART(year, date)) + '_' + CONVERT(VARCHAR,DATEPART(wk, date)) AS Year_Week,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY CONVERT(VARCHAR,DATEPART(year, date)) + '_' + CONVERT(VARCHAR,DATEPART(wk, date))
ORDER BY CONVERT(VARCHAR,DATEPART(year, date)) + '_' + CONVERT(VARCHAR,DATEPART(wk, date))

or (probably better), just treat them as entirely separate fields:

SELECT 
    DATEPART(year, date) AS Year,
    DATEPART(wk, date) AS Week,
    SUM((kg * rep * sett)) AS weight,
    SUM(kg / max * rep * sett) AS avg,
    SUM((rep * sett)) AS reps,
    MAX(kg / max) AS peak
FROM
    Test
WHERE
    date BETWEEN @0 AND @1 AND exercise < 4
GROUP BY DATEPART(year, date), DATEPART(wk, date)
ORDER BY DATEPART(year, date), DATEPART(wk, date)