CODEWITHSUNDEEP
sqlsql-serversql-order-byrdbms
Pravin
Pravin

Reputation: 371

How to SELECT distinct data with required ORDER?

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?

Upvotes: 1

Views: 103

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5165

You can use the below query,

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

You have not selected ord in the query, but used in the order by clause so it will throw an error. Second thing when you are aggregating (sum,count,avg,min or max) with respect to other columns, you have to use group by clause

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271161

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

Upvotes: 3

Related Questions