How do I get the top ten in sql?
This delow request throws 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:
ORDER BY is not valid in views, inline functions, views, subqueries, and general table expressions unless TOP or FOR XML is specified.
-1
a source to share
4 answers
deduce order from view "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
+2
a source to share
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
+1
a source to share
Just specify the order inside the 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 is between @ date1 and @ date2
and [user] in (select USERNAME
from scr_CustomerAuthorities ORDER ANYTHING)
group by Page order by [VisitingCount] desc) t
0
a source to share