ORDER BY rsc.RadioServiceCodeId, rsc.RadioServiceCode + ' - ' + rsc.RadioService
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.
While they are not the same thing, in one sense DISTINCT
implies a GROUP BY
, because every DISTINCT
could be re-written using GROUP BY
instead. With that in mind, it doesn't make sense to order by something that's not in the aggregate group.
For example, if you have a table like this:
col1 col2
---- ----
1 1
1 2
2 1
2 2
2 3
3 1
then try to query it like this:
SELECT DISTINCT col1 FROM [table] WHERE col2 > 2 ORDER BY col1, col2
That would make no sense, because there could end up being multiple col2
values per row. Which one should it use for the order? Of course, in this query you know the results wouldn't be that way, but the database server can't know that in advance.
Now, your case is a little different. You included all the columns from the order by
clause in the select
clause, and therefore it would seem at first glance that they were all grouped. However, some of those columns were included in a calculated field. When you do that in combination with distinct, the distinct
directive can only be applied to the final results of the calculation: it doesn't know anything about the source of the calculation any more.
This means the server doesn't really know it can count on those columns any more. It knows that they were used, but it doesn't know if the calculation operation might cause an effect similar to my first simple example above.
So now you need to do something else to tell the server that the columns are okay to use for ordering. There are several ways to do that, but this approach should work okay:
SELECT rsc.RadioServiceCodeId,
rsc.RadioServiceCode + ' - ' + rsc.RadioService as RadioService
FROM sbi_l_radioservicecodes rsc
INNER JOIN sbi_l_radioservicecodegroups rscg
ON rsc.radioservicecodeid = rscg.radioservicecodeid
WHERE rscg.radioservicegroupid IN
(SELECT val FROM dbo.fnParseArray(@RadioServiceGroup,','))
OR @RadioServiceGroup IS NULL
GROUP BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService
ORDER BY rsc.RadioServiceCode,rsc.RadioServiceCodeId,rsc.RadioService
When you define concatenation you need to use an ALIAS for the new column if you want to order on it combined with DISTINCT Some Ex with sql 2008
--this works
SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName
from SalesLT.Customer c
order by FullName
--this works too
SELECT DISTINCT (c.FirstName + ' ' + c.LastName)
from SalesLT.Customer c
order by 1
-- this doesn't
SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName
from SalesLT.Customer c
order by c.FirstName, c.LastName
-- the problem the DISTINCT needs an order on the new concatenated column, here I order on the singular column
-- this works
SELECT DISTINCT (c.FirstName + ' ' + c.LastName)
as FullName, CustomerID
from SalesLT.Customer c
order by 1, CustomerID
-- this doesn't
SELECT DISTINCT (c.FirstName + ' ' + c.LastName) as FullName
from SalesLT.Customer c
order by 1, CustomerID