How to SELECT distinct data with required ORDER?

Question

I had created table t1(ca char(1), cb char(10), test char(20), ord char(20)) and I want to get distinct ca+cb with an order by ord.

To get data I wrote query as:

select distinct ca + cb as exp, test
from table
order by ord, exp

Error received:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified. `

Also tried to with inner query as

select exp, test
from ( select distinct ca + cb as exp, ord, test
from ttemp
order by ord, exp)

Error received:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

How can I select distinct data with required ORDER?

Answer 1

Try using group by. Of course, naively, this would be:

select (ca + cb) as exp, test
from table
group by (ca + cb), test
order by ord, exp

And you will get an error, because ordis not in the select or group by. So, you need an aggregation function. For instance:

select (ca + cb) as exp, test
from table
group by (ca + cb), test
order by min(ord), exp;

I should note that you can trivially solve the problem by including ord in the select, with either select distinct or group by:

select distinct ca + cb as exp, test, ord
from table
order by ord, exp