SQL Query to sort date based on two column-CodePudding

I have following data in table RATING and i want to sort this based on Rating and Year.

Database MS SQL Server

Unsorted data in Table

ID  PlayerName  Rating  Year    
1   A   8   2022    
2   B   8   2022
3   C   0   2022
4   A   7   2020
5   B   6   2020
6   C   6   2020
7   E   5   2020
8   D   5   2020
9   D   5   2022

Data should show as below

ID  PlayerName  Rating  Year
1   A   8   2022
2   B   8   2022
3   D   5   2022
9   C   0   2022
4   A   7   2020
5   B   6   2020
6   C   6   2020
7   E   5   2020
8   D   5   2020

I am not able to get it right i used following Query

SELECT ID, PlayerName, Rating, Year 
FROM   RATING 
Where Year IN (SELECT Year from Rating) 
order by year DESC

but it doesn't get the correct order as i am not able to use order by clause in sub query as it generates error The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Event two column sort is not working properly

SELECT ID, PlayerName, Rating, Year 
FROM   RATING 
order by Rating, Year

CodePudding user response：

You can try to ORDER BY year DESC first then Rating DESC

SELECT ID, PlayerName, Rating, [Year] 
FROM RATING 
order by [Year] DESC,Rating DESC

Year is a keyword in sqlserver, I would use brackets to contain it.

CodePudding user response：

Your attempt is not bad so far. But there are two things you need to change. First: The column with the higher priority for your sorting is the column "year", so this has to be used first, and then the column "rating". Second: You must add the key word "DESC" to begin with the newest year and the highest ranking. So your query should be this one:

SELECT * FROM rating ORDER BY year DESC, rating DESC;

You can see this is working here: db<>fiddle

If you can rename the columns on your DB, I recommend to do not use SQL key words as column names (in your example, this the column "year") and to do not use column names that are identic to the table names (in your case, you could rename the table "rating" to "ratings" or similar). Both of this is of course possible, but could sometimes be bad to read and let increase the risk of issues.