Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Get Previous and Next Row Values from Sequence

May 8, 2016
Introduction:

Here I will explain how to get previous and next row values from sequence in
sql server or find next and previous records from table in sql server or access previous and next row values from in sql server. To get next and previous records from table data in sql server we need to write query to get max and min value from table data based on value in sql server.
Description:

In one application I got requirement to get next and previous posts based on current post from table in sql server but that record id values not in sequence. To get previous and next records based on current record value I tried to get max and min value from sql server table based on current value like as shown below


SELECT (SELECT  MAX(t.topicid) PreviousID
FROM @temp t
WHERE topicid < @ptopicid) PreviousId, (SELECT  MIN(et.topicid) NextID
FROM @temp et
WHERE topicid > @ptopicid) NextId

If you observe above query we are getting previous and next records based on current record id by taking max and min values in sql server.

Now we will see how to get next and previous records from table in sql server with example.


DECLARE @temp TABLE (topicid int, topicname varchar(50))
--Insert Data in table
INSERT INTO @temp(topicid, topicname)
values(1,'sql joins'),
(5, 'sql insertion'),
(10,'sql creation'),
(13,'sql select'),
(16,'sql update'),
(24,'sql triggers')

DECLARE @ptopicid INT
SET @ptopicid = 10
-- Get Table Records
SELECT * FROM @temp
-- Get Previous and Next Record Ids
SELECT (SELECT  MAX(t.topicid) PreviousID
FROM @temp t
WHERE topicid < @ptopicid) PreviousId, (SELECT  MIN(et.topicid) NextID
FROM @temp et
WHERE topicid > @ptopicid) NextId

If you observe above query we insert data in temp table and getting previous and next record id values based on topicid.

Output to Get Previous and Next Rows

Now we will run and see the output of above query that would be like as shown below


I hope it helps you to get next and previous records from table data in sql server.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

0 comments :

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.