How to sort last four weeks between two years in SQL?-CodePudding

I am having issue sorting week numbers (last four weeks with current week) in proper order.

I want to have output in this order **49 50 51 52 1 **

SELECT
    --- Calculate Percentage ---
    CONCAT(ROUND(COUNT(CASE WHEN db.PRESENT=1 THEN 1 ELSE NULL END)* 100.0 / COUNT(*), 2), 0) AS Present,
    --- Convert to Weeks ---
    DATEPART(iso_week, CONVERT(varchar(7), db.Day, 110)) AS PerWeek
FROM db.Attending
WHERE(db.Day>=DATEADD(WEEK, -5, GETDATE()))AND(db.Day<=GETDATE())
GROUP BY DATEPART(iso_week, CONVERT(varchar(7), db.Day, 110))
ORDER BY PerWeek ASC

My output looks like this:

Present    PerWeek
6.55       51
71.02      50
73.33      49
75.24      48
48.93      1

Desired order.

Present    PerWeek
48.93      1
28.05      52
6.55       51
71.02      50
73.33      49

Thanks in advance

CodePudding user response：

After jarlh advice I have modified code like this:

SELECT
    --- Calculate Percentage ---
    CONCAT(ROUND(COUNT(CASE WHEN db.PRESENT=1 THEN 1 ELSE NULL END)* 100.0 / COUNT(*), 2), 0) AS Present,
    --- Format Output as YYYY-ISO_WEEK ---
    CONCAT(DATEPART(year, pl.JP_TAG), '-', DATEPART(iso_week, CONVERT(varchar(7), pl.JP_TAG, 110))) AS ISOWeekYear
FROM db.Attending
WHERE(db.Day>=DATEADD(WEEK, -5, GETDATE()))AND(db.Day<=GETDATE())
GROUP BY DATEPART(iso_week, CONVERT(varchar(7), db.Day, 110)), DATEPART(year, db.Day)
ORDER BY DATEPART(year, db.Day) ASC, DATEPART(iso_week, CONVERT(varchar(7), db.Day, 110)) ASC

Now I have output as desired.