CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Olivarsham
Olivarsham

Reputation: 1731

sql server order by TOP 100 PERCENT in SELECT query

This is my query: 

select * from   
(select name,empID,salary,[deducted salary] = salary-7000 from tblEmpDetails   
order by Joined_Date) TmpTbl where [decucted salary] > 50000

It was giving 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.

Then I changed my query to: 

select * from   
(select TOP 100 PERCENT name,empID,salary,[deducted salary] = salary-7000   
from tblEmpDetails order by Joined_Date) TmpTbl where [decucted salary] > 50000

Now its is working fine.
My query is using TOP 100 is the correct method or any other work around is there for this?

Upvotes: 1

Views: 4873

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

You would have wanted the ORDER BY in the outer query, e.g.

select name,empID,salary,[deducted salary] from   
(select name,empID,salary,[deducted salary] = salary-7000, Joined_Date
   from tblEmpDetails   
) TmpTbl where [decucted salary] > 50000
order by Joined_Date

EDIT - Yes you need to include Joined_Date in the inner query to sort by it on the outer query, as well as explicitly listing only the 4 columns desired instead of *.

But you could also have written the query in one level

  select name,empID,salary,[deducted salary] = salary-7000
    from tblEmpDetails
   where salary-7000 > 50000
order by Joined_Date

Note that salary-7000 although repeated in the query is only evaluated once by SQL Server because it is smart enough to use it twice.

Upvotes: 5

Related Questions