How to limit rows in query SQL Server Management Studio?

Question

I am new SQL user. I am trying to limit the number of rows pulled on SQL Server Management Studio using the following query.

SELECT [Column A] 
FROM [DB].[Ordering In DB].[Table] 
WHERE [Column B] = 30 
LIMIT 3;

I get the following result.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'LIMIT'

Can LIMIT not be used in SQL Server Management Studio? In the end I only want to get to 3 or x number of rows. How do I do this?

Some dbms' such as Sql Server and Oracle do not support `limit`. Have you tried `select top 3 column_a from...` — Isolated, Oct 06 '22 at 16:54
For SQL Server, use `SELECT TOP (3) .......` - `LIMIT` is a MySQL/PostgreSQL custom feature - not available in T-SQL / SQL Server — marc_s, Oct 06 '22 at 17:18
@Isolated, SQL Server supports both TOP and the ISO-standard FETCH, where it's part of the ORDER BY clause. — Dan Guzman, Oct 06 '22 at 17:35
@Isolated, with OFFSET..FETCH: `ORDER BY id OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY`. — Dan Guzman, Oct 06 '22 at 17:45
@DanGuzman I know that. But I think you're original comment read "LIMIT" not "FETCH" and then you changed it. Once I saw it change (or my vision corrected) then I removed my comment. — Isolated, Oct 06 '22 at 17:50
@Isolated, sorry, the reference to limit was a typo and I had changed it before your comment to me. — Dan Guzman, Oct 06 '22 at 17:57
Since you are asking specifically about `SQL Server Management Studio`: Settings > Query execution > Sql Server > Set rowcount: — Stu, Oct 06 '22 at 20:22

score 0 · Answer 1 · answered Oct 06 '22 at 17:55

0

You can use top 1,2,x

like

SELECT TOP 10 [Column A] 
FROM [DB].[Ordering In DB].[Table] 
WHERE [Column B] = 30 ;

Will be showed just 10 first rowns.

answered Oct 06 '22 at 17:55

Italo Menezes

score 0 · Answer 2 · answered Oct 06 '22 at 20:10

Here are the two things to limit your rows in SELECT query:

Use TOP(Number_of_Rows_To_Be_Fetched):

SELECT TOP(5) [Column A] FROM [Table] WHERE [Column B] = 30

This will fetch only 5 records.

Here, OFFSET means the number of rows you want to skip from top. FETCH means the next number of rows you want to fetch.

SELECT [Column A]
FROM [Table] 
WHERE [Column B] = 30
ORDER BY [Column A]
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY

This query will skip first 2 rows and fetch next 3 rows.

2 Answers2