If you’re getting an error that reads “Incorrect syntax near the keyword ‘DISTINCT’” when using the DISTINCT clause in SQL Server, it could be that you’ve put the DISTINCT clause in the wrong position.
When using the DISTINCT clause, it must be the first item in the SELECT list.
Therefore, to fix this error, check the position of the DISTINCT keyword. If it’s not the first item in the SELECT list, move it to the front so that it is the first item in the SELECT list.
Example of Error
Here’s an example of code that produces the error:
SELECT
ProductPrice, DISTINCT VendorId
FROM Products
ORDER BY ProductPrice;Result:
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'DISTINCT'.
The error doesn’t really explain why there’s a syntax error, just that there is one. But in this case, I know that it’s because the DISTINCT keyword is in the wrong position.
Solution
We can fix the above error simply by moving the DISTINCT keyword to the front of the SELECT list:
SELECT
DISTINCT ProductPrice, VendorId
FROM Products
ORDER BY ProductPrice;Result:
ProductPrice VendorId ------------ -------- 9.99 1004 10 1003 11.99 1001 12.45 1004 14.75 1001 25.99 1001 33.49 1002 55.99 1003 245 1003
This time the query ran without error.