FROM (
        SELECT a.Title, a.ID FROM TableA a
        LEFT JOIN TableB b 
        ON a.ID = b.XID) c
        CASE WHEN c.ID > 10 THEN c.Title
        ELSE c.ID

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.

  • then both ID and Title should be in same type, e.g: INT - what makes you say that?
    – Vérace
    Feb 29, 2020 at 12:23
  • 1
    please fix your query (the current one will never work) Feb 29, 2020 at 12:31
  • @Vérace I don't know for sure, but I think it's because case expect same datatype, am I right? Feb 29, 2020 at 12:33
  • No, you are incorrect - you can have combinations of DISTINCT with an INTEGER and a VARCHAR for example! p.s. welcome to the forum! :-)
    – Vérace
    Feb 29, 2020 at 12:39
  • 1
    you still have no "h" table in FROM Feb 29, 2020 at 12:40

1 Answer 1


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

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.

  • SQL Server will need to compare Title with ID to sort your final result set, sql compare ID and Title in CASE? why? Feb 29, 2020 at 13:27
  • because this expression (your CASE) is like a virtual column which may contain Tittle or ID depending on ID value. And you use sort by this column. Feb 29, 2020 at 13:30
  • 2
    @MehdiDehghani, you can think of a case expression as a kind of function, it maps a set of types to a return type. Regardless of input types, the return type must always be the same. Feb 29, 2020 at 13:35
  • 1
    Yes, @Lennart is corret. The result should have the same data type even if you wouldn't use order by. Feb 29, 2020 at 13:50
  • 4
    @MehdiDehghani: Just to offer you another explanation, a 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.
    – Andriy M
    Feb 29, 2020 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.