0
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.

  • then both ID and Title should be in same type, e.g: INT - what makes you say that? – Vérace Feb 29 at 12:23
  • 1
    please fix your query (the current one will never work) – Nikita Feb 29 at 12:31
  • @Vérace I don't know for sure, but I think it's because case expect same datatype, am I right? – Mehdi Dehghani Feb 29 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 at 12:39
  • 1
    you still have no "h" table in FROM – Nikita Feb 29 at 12:40
3

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://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15

| improve this answer | |
  • SQL Server will need to compare Title with ID to sort your final result set, sql compare ID and Title in CASE? why? – Mehdi Dehghani Feb 29 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. – Nikita Feb 29 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. – Lennart Feb 29 at 13:35
  • 1
    Yes, @Lennart is corret. The result should have the same data type even if you wouldn't use order by. – Nikita Feb 29 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 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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