Resolving "ORDER BY items must appear in the select list if SELECT DISTINCT is specified" in SQL Server
It is possible that while working with SQL Server, you may get the error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
This statement can be misleading, especially for new developers who are being exposed to SQL.
In the post we will describe this error, explain why you get it, and show you how to fix it by giving real examples.
How the Error Works
This error occurs when you are using SELECT DISTINCT with an example that includes ORDER BY, and you are trying to order by a column that is not represented in the SELECT list.
Why it happens:
SELECT DISTINCT is used to remove duplicate rows.
With DISTINCT, SQL Server matches up the unique values of the column list, and when outputting the unique values, SQL Server needs to guarantee that the values are unique for all columns that are part of the selection and ordering criteria.
When you try to sort by a column that is not listed in the SELECT DISTINCT list, then SQL Server has no way to know how to order those distinct rows.
An Example That Will Cause The Error
Select distinct CountryId
from CountryMaster order
by CountryName
This causes the error message:
How to fix this we add column name which is used to order by
Select distinct CountryId,CountryName
from CountryMaster order
by CountryName
🙏 Thank You for Reading!
Thank you for taking the time to read this blog!
If you have any questions or need help with something, feel free to drop a message in the comments or contact section. I’ll get back to you as soon as possible.
Happy Learning! 😊