How to order by for customer week number which contains year number too

Question

I have a table with one row

employee(date)

date
52 week in 2021
23 week in 2022
34 week in 2021
1 week in 2022
52 week in 2022

I tried the below query but it's not working

select date from employee order by date desc

I want to order by in desc for above column. I'm expecting output like below

date
52 week in 2022
23 week in 2022
1 week in 2022
52 week in 2021
34 week in 2021

Answer 1

Extracting year and week part and then performing sort:

SELECT *
FROM tab
ORDER BY RIGHT(date,4) DESC, CAST(LEFT(date,2) AS INT) DESC