CODEWITHSUNDEEP
sqlsql-servert-sql
Nikhar Gaurav
Nikhar Gaurav

Reputation: 19

Use of top over the subquery with order by inside the subquery

Select top(1) track_id 
From
(select track_id, sum(sale_amt) total 
from dbo.Track_Daily_Sales 
group by track_id 
order by total
) abc

I am getting an error i.e 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 want to use top over the subquery with order by inside the sub query, is this possible...

Upvotes: 0

Views: 91

Answers (1)

sgeddes
sgeddes

Reputation: 62841

For your specific example, you don't need the subquery at all if ties aren't a concern:

select top 1 track_id
from dbo.Track_Daily_Sales 
group by track_id 
order by sum(sale_amt)

Here's an approach with rank() to handle ties:

select track_id
from (
  select track_id, rank() over (order by sum(sale_amt)) rn 
  from Track_Daily_Sales 
  group by track_id 
  ) t
where rn = 1

Upvotes: 1

Related Questions