How to start from particular row in sql and skip previous and take all next rows ?

Question

0.00/5 (No votes)

See more:

Hi ..

I want to take take elements of sql rows from particular id and next from that point and want to skip previous records of that.

Please suggest .

What I have tried:

SQL

Declare @TopLocationCode NVARCHAR(15) ='Company'
Declare @LeastLoactionCode NVARCHAR(15) ='Area'
Declare @SearchLocationText NVARCHAR(100) = NULL
Declare @SearchLocationType INT = NULL
DECLARE @Rank INT

	SELECT	@Rank = Rank, 
			@SearchLocationType = CASE WHEN @SearchLocationType IS NULL THEN lt.LocationTypeID ELSE @SearchLocationType END
	FROM LocationType lt 
	WHERE LTRIM(RTRIM(lt.Code)) = LTRIM(RTRIM(@LeastLoactionCode));

       WITH HierarchyP
		   AS ( 
				SELECT l.LocationID,l.ParentLocationID
				FROM Location l
				INNER JOIN LocationType lt ON l.LocationTypeID = lt.LocationTypeID
				WHERE l.IsInActive = 0 AND LTRIM(RTRIM(lt.Code)) = LTRIM(RTRIM(@TopLocationCode))  

				UNION ALL

				SELECT l.LocationID, l.ParentLocationID
				FROM Location l
				INNER JOIN LocationType lt ON l.LocationTypeID = lt.LocationTypeID
				INNER JOIN HierarchyP ON				 
				l.ParentLocationID = HierarchyP.LocationID
				WHERE l.IsInActive = 0 AND Rank <= @Rank
		)

        SELECT DISTINCT
			l.LocationID,
			l.Code,
			l.Name,
			l.ParentLocationID ,
			lt.Code AS LocationTypeCode,
			lt.Rank  
		FROM 
		Location l INNER JOIN HierarchyP h ON l.LocationID = h.LocationID
		INNER JOIN LocationType lt ON lt.LocationTypeID = l.LocationTypeID
		WHERE ((ISNULL(@SearchLocationText,'') = '') 
		OR (l.LocationTypeID <> @SearchLocationType) 
		OR (l.LocationTypeID = @SearchLocationType AND l.Name LIKE '%' + @SearchLocationText + '%'))

Posted 25-Feb-16 0:55am

Torakami

Updated 25-Feb-16 1:27am

Manas_Kumar

v2

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)

Nigam,Ashish · Answer 1 · 2016-02-25T01:22:00

Solution 1

Hi,

You can make a variable and store lastId of lastRow, then put a where condition on your query to get records after that Id

like:
@lastId=10;

select * from yourtable where yourTableId>@lastId

Ashish Nigam

Posted 25-Feb-16 1:22am

Nigam,Ashish

Manas_Kumar · Answer 2 · 2016-02-25T01:27:00

Try with below options. Following query fetches 5 records from 6 to 10:

Option1: Use Row_Number

SQL

SELECT  *
FROM    (	  
		  SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY LocationID) AS RowNum,			
			l.LocationID,
			l.Code,
			l.Name,
			l.ParentLocationID ,
			lt.Code AS LocationTypeCode,
			lt.Rank  
		FROM 
			Location l INNER JOIN HierarchyP h ON l.LocationID = h.LocationID
			INNER JOIN LocationType lt ON lt.LocationTypeID = l.LocationTypeID
			WHERE ((ISNULL(@SearchLocationText,'') = '') 
			OR (l.LocationTypeID <> @SearchLocationType) 
			OR (l.LocationTypeID = @SearchLocationType AND l.Name LIKE '%' + @SearchLocationText + '%'))			
        ) AS TempResult
WHERE   RowNum > 5
    AND RowNum <= 10
ORDER BY RowNum

Option2: Use Offset and Fetch

SQL

SELECT 		l.LocationID,
			l.Code,
			l.Name,
			l.ParentLocationID ,
			lt.Code AS LocationTypeCode,
			lt.Rank  
		FROM 
			Location l INNER JOIN HierarchyP h ON l.LocationID = h.LocationID
			INNER JOIN LocationType lt ON lt.LocationTypeID = l.LocationTypeID
			WHERE ((ISNULL(@SearchLocationText,'') = '') 
			OR (l.LocationTypeID <> @SearchLocationType) 
			OR (l.LocationTypeID = @SearchLocationType AND l.Name LIKE '%' + @SearchLocationText + '%'))
ORDER BY l.LocationID ASC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY

Note: Offset-Fetch only works SQLSrever 2012 on wards.