-4

The inner query runs successfully alone, but when I run it as a subquery, an error pop up.

My query:

Select  TOP 3 * 
FROM (select 
        fr.rating
        ,   sp.SpId
        ,sp.FName
        ,sp.LName
        ,sp.DpUrl
        ,sp.CoverUrl
        ,sp.city
        ,s.ServiceTitle 
    from ServiceProvider as sp 
        inner join SpServices as ss on sp.SpId=ss.SpId 
        inner join Services as s on ss.ServiceId=s.ServiceId 
        inner join FinalRating as fr on fr.spid=sp.SpId 
    where s.ServiceTitle='Laptop Repairing' ORDER BY fr.rating DESC)
  • Try putting the ending parenthesis after 'Laptop Repairing' instead of after desc. – Scott Hodgin Oct 15 at 14:32
  • after putting parenthesis before order by clause following error pop up "Msg 156, Level 15, State 1, Line 60 Incorrect syntax near the keyword 'ORDER'." – Tayyab Hussain Oct 15 at 14:33
  • You are creating a derived table - try putting an alias on your derived table. Ex: 'Laptop Repairing') a – Scott Hodgin Oct 15 at 14:35
  • 3
    You don't need a subquery to select the top 3. – ypercubeᵀᴹ Oct 15 at 14:58
0

Use this, sql server really doesn't like your ORDER BY in the subquery. And as tiolbe already in the cmment every derived table needs a alias

Select  TOP 3 * 
FROM (select 
        fr.rating
        ,   sp.SpId
        ,sp.FName
        ,sp.LName
        ,sp.DpUrl
        ,sp.CoverUrl
        ,sp.city
        ,s.ServiceTitle 
    from ServiceProvider as sp 
        inner join SpServices as ss on sp.SpId=ss.SpId 
        inner join Services as s on ss.ServiceId=s.ServiceId 
        inner join FinalRating as fr on fr.spid=sp.SpId 
    where s.ServiceTitle='Laptop Repairing' ) t1
ORDER BY rating DESC
| improve this answer | |
  • giving this error now Msg 4104, Level 16, State 1, Line 78 The multi-part identifier "fr.rating" could not be bound. – Tayyab Hussain Oct 15 at 14:43
  • ok the alias fr i sunknown, try the changed answer – nbk Oct 15 at 14:45
  • I want the inner query result in desc order by fr.rating then i want to select top 3 from that result – Tayyab Hussain Oct 15 at 14:45
  • the order in a subqueries is not possible, and gives an error, because tables are by definition unsorted, and it also doesn't matter where you put it – nbk Oct 15 at 14:46
  • so if i put order by outside sub query then how can i use order by on fr.rating column – Tayyab Hussain Oct 15 at 14:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.