Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
SELECT DISTINCT *
FROM (
SELECT a.Title, a.ID FROM TableA a
LEFT JOIN TableB b
ON a.ID = b.XID) c
ORDER BY
CASE WHEN c.ID > 10 THEN c.Title
ELSE c.ID
END
ASC
And if I move DISTINCT to nested select, then both ID and Title should be in same type, e.g: INT, but they are not.
Both ID and Title are in the selected columns, what is the problem?
Also with this change CASE WHEN 1 <> 1 query runs without problem.
The real issue is caused by data type conversion. If you add this expression to the select list:
CASE WHEN c.ID > 10 THEN c.Title
ELSE c.ID
END
you will see another error. Something like: "Conversion failed when converting the nvarchar value 'Some title' to data type int". According to data type precedence when you compare varchar with int values (SQL Server will need to compare Title with ID to sort your final result set) varchar should be converted to int because int has higher precedence.
https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15
CASE expression returns a value not a reference. Any references inside a CASE expression are evaluated. In the end you get a single value as the result. A value must have a specific type. When the different branches (THEN, ELSE) evaluate to different types, then there are rules which type should be chosen for the result. When the choice is between a string type and a numeric type, like in your scenario, SQL Server will choose the result to be a number. And if the string doesn't look like a number, you'll just get an error.
then both ID and Title should be in same type, e.g: INT- what makes you say that?DISTINCTwith anINTEGERand aVARCHARfor example! p.s. welcome to the forum! :-)