how to get top ten result in sql?

Question

This delow query give me some error:

declare @date1 nvarchar(100) , @date2 nvarchar(100)

select @date1='2009-04-20', @date2='2009-05-20'

select top 10 t.VisitingCount , t.Page
    from (
           select  Count(Page) as VisitingCount,Page
               from scr_SecuristLog   
               where Date between @date1 and @date2  
                   and [user] in (select USERNAME             
                                      from scr_CustomerAuthorities
                                 )  
               group by Page order by [VisitingCount] desc 
         ) t 

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.

Answer 1

I think you missed Comma

select top 10 t.VisitingCount , t.Page from

in above line after t.VisitingCount

Answer 2

declare @date1 nvarchar(100) , @date2 nvarchar(100)

select @date1='2009-04-20', @date2='2009-05-20'

select t.VisitingCount, t.Page from(
select top 10 Count(Page) as VisitingCount,Page from scr_SecuristLog   
where Date between @date1 and @date2  
and [user] in(select USERNAME             
    from scr_CustomerAuthorities )  
group by Page order by [VisitingCount] desc ) t order by t.VisitingCount desc

Answer 3

Just specify some order by inside your inline view:

declare @date1 nvarchar(100) , @date2 nvarchar(100)

select @date1='2009-04-20', @date2='2009-05-20'

select top 10 t.VisitingCount , t.Page from( select Count(Page) as VisitingCount,Page from scr_SecuristLog
where Date between @date1 and @date2
and [user] in(select USERNAME
from scr_CustomerAuthorities ORDER BY whatever-column(s)-you-need )
group by Page order by [VisitingCount] desc ) t

Answer 4

pull the order by out of the derived table "t"

try this:

declare @date1 nvarchar(100) , @date2 nvarchar(100)

select @date1='2009-04-20', @date2='2009-05-20'

select top 10 t.VisitingCount , t.Page
    from (
           select  Count(Page) as VisitingCount,Page
               from scr_SecuristLog   
               where Date between @date1 and @date2  
                   and [user] in (select USERNAME             
                                      from scr_CustomerAuthorities
                                 )  
               group by Page
         ) t 
     order by [VisitingCount] desc