CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Smaug
Smaug

Reputation: 2673

How can use order by column with out using top in sub query

I've this query and it's getting error "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

I've solved this issue with using TOP in the inner query, but some cases I wouldn't measure the record count. Is there any other possibility to sort it out ? 

select *
from rs_column_lang
where column_id in (
    select column_id
    from rs_column
    order by column_id
)

Upvotes: 0

Views: 122

Answers (5)

VitaliyG
VitaliyG

Reputation: 1857

You should optimize your query like this

select A.*
from rs_column_lang A
  inner join rs_column B on A.column_id = B.column_id
order by A.column_id

because this is simple inner join you will get significant performance improvement if DB optimizer not recognized inner join

Upvotes: 0

Joe Enos
Joe Enos

Reputation: 40393

Having an order by in your inner query doesn't make sense. You're checking your outer query for matching records, but you're reading the entire table on your inner query to find the matches. Sorting that inner query serves no purpose, so SQL Server yells at you for it.

Like the other answers indicate, assuming your goal is to sort the results, your order by should be part of your outer query, not the inner one.

The reason a top would work in the inner query is that it would change the results of the query, depending on what you're ordering by. But just changing the order would not change the results.

Upvotes: 3

tezzo
tezzo

Reputation: 11105

column_id contains the same data in rs_column_lang and rs_column so you can simply move your ORDER BY in the outer query:

SELECT * FROM rs_column_lang
WHERE column_id IN (
    SELECT column_id FROM rs_column
)
ORDER BY column_id

Upvotes: 0

Yaugen Vlasau
Yaugen Vlasau

Reputation: 2218

select  * from rs_column_lang
where column_id in (
select column_id from rs_column
)
order by column_id

Upvotes: 1

bvr
bvr

Reputation: 4826

Try this

select  * from rs_column_lang
where column_id in 
(
select column_id from rs_column
)
order by column_id

Upvotes: 1

Related Questions