Is LIMIT not allow in sql server 2008?

Question

I have a query

  SELECT employeedept, execoffice_status, employee, COUNT(*) AS 'employeetotal', YEAR_cse1 =YEAR(execoffice_date)
FROM CSEReduxResponses
WHERE execoffice_status = 1
GROUP BY employeedept, execoffice_status, YEAR(execoffice_date), employee 
order by [YEAR_cse1],
LIMIT 20

Which when I add the LIMIT it gives me a error "Incorrect syntax near '20'.". Is there another way to get the top 20?

I have Microsoft SQL Server Management Studio 10.0.2531.0, SQL server 2008.

Maybe I'm gonna get my first flag, but this took approximately 5 seconds to find the answer by searching "sql server limit" — Tim Lehner, Jun 10 '14 at 23:13

score 1 · Accepted Answer · answered Jun 10 '14 at 23:11

you are probably looking for top

SELECT TOP 20 employeedept, execoffice_status, employee,
COUNT(*) AS 'employeetotal',YEAR_cse1 =YEAR(execoffice_date)
FROM CSEReduxResponses
WHERE execoffice_status = 1
GROUP BY employeedept, execoffice_status, YEAR(execoffice_date), employee 
order by [YEAR_cse1]

score 0 · Answer 2 · answered Jun 10 '14 at 23:13

If you read the documentation for select, you'll see that it uses the keyword top, whose documentation says that it

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2012. When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement or affected by an INSERT, UPDATE, MERGE, or DELETE statement.

So you can get what you want by saying something like

select top 20
       *
from foo

Is LIMIT not allow in sql server 2008?

2 Answers2