How to use limit in ms SQL ?

Question

0.00/5 (No votes)

See more:

hi guys i used limit to fetch rows with range ? but my code query do have an error heres my code:

What I have tried:

cmd.CommandText = "select * FROM tblstudinfo LIMIT 1,2"
rdr = cmd.ExecuteReader

it said "incorrect syntax near 1"

Posted 24-Jan-17 1:38am

Member 12919944

Updated 24-Jan-17 5:23am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2017-01-24T05:23:00

Solution 2

If you're using MS SQL Server 2012 or later, you can use OFFSET and FETCH:

SQL

SELECT *
FROM tblstudinfo
ORDER BY studentid
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY

ORDER BY Clause (Transact-SQL)[^]

Posted 24-Jan-17 5:23am

Richard Deeming

ZurdoDev · Answer 2 · 2017-01-24T01:58:00

Solution 1

In MS Sql you use the TOP keyword.

SQL

SELECT TOP 1 * FROM tblStudInfo

Posted 24-Jan-17 1:58am

ZurdoDev

Comments

Member 12919944 24-Jan-17 8:09am

but it will only select from the top most ? i want to select from different range of rows. for example rows 5 to 10 only or 1 to 5

ZurdoDev 24-Jan-17 8:15am

In that case, you use ROW_NUMBER https://msdn.microsoft.com/en-us/library/ms186734.aspx.

Member 12919944 24-Jan-17 8:19am

i tried it but it said invalid column name ROW_NUMBER

Member 12919944 24-Jan-17 8:19am

cmd.CommandText = "select * FROM tblstudinfo where ROW_NUMBER BETWEEN 1 and 2"

ZurdoDev 24-Jan-17 8:21am

You need to read the documentation. That is not the syntax for ROW_NUMBER.

Member 12919944 24-Jan-17 8:33am

SELECT
ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
name, recovery_model_desc
FROM sys.databases
WHERE database_id < 5;

where did he get databse_id?

heres mine cmd.CommandText = "select ROW_NUMBER() OVER(ORDER BY studentid ASC) AS Row#,number FROM tblstudinfo where database_id < 2"

and it said Invalid column name 'database_id'.

ZurdoDev 24-Jan-17 9:08am

You want do do something like this:

SELECT *
FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY studentid ASC) AS Row#,
number
FROM tblstudinfo
) x
WHERE x.Row# BETWEEN 2 AND 4

Member 12919944 24-Jan-17 9:54am

it works dude. where did the x came from ??

ZurdoDev 24-Jan-17 10:05am

x is aliasing the derived table. SELECT * FROM (stuff inside here) AS x

Member 12919944 24-Jan-17 10:30am

this part is it ? ") x"

ZurdoDev 24-Jan-17 10:45am

Yes, what is inside the parentheses is treated as a table outside. So, you just have to give it a name. I use x. It could be anything.