CODEWITHSUNDEEP
sql-server
Log Bwd
Log Bwd

Reputation: 15

Display previous and next record based on where clause value

 Id Name
  1 Jason 
  2 Shivam Mack
  3 Saim Chavez
  4 Marius Cash
  5 Rhys Olsen
  6 Alaina Plummer
  7 Philip Goddard
  8 Aaran Mckeown
  9 Riaz Stone
  10 Conrad Mclean
  11 Mae Austin
  12 Milli Clifford
  13 Kajetan Barajas
  14 Missy Malone
  15 Kieron Steele
  16 Margot Haley
  17 Lucien Villalobos
  18 Afsana Rodrigues
  19 Dionne Gilmour
  20 Robbie Harris

I have this table with list of names, what I need is to display the first three rows and the next three depending on the Id set in the where close. Example if the Id set in the where close is 11, it will display the following:

  8 Aaran Mckeown
  9 Riaz Stone
  10 Conrad Mclean
  12 Milli Clifford
  13 Kajetan Barajas
  14 Missy Malone

When the value in the where clause is 3, it will display the following:

  1 Jason 
  2 Shivam Mack
  4 Marius Cash
  5 Rhys Olsen
  6 Alaina Plummer

Upvotes: 1

Views: 54

Answers (3)

Nick
Nick

Reputation: 147156

if your Id values are consecutive, you can simply use something like:

DECLARE @ID INT;
SET @ID = 3;
SELECT *
FROM names
WHERE Id BETWEEN @ID - 3 AND @ID + 3
  AND Id != @ID

If the Id values are not consecutive, you can use CTEs to generate a row number for each row and then find the row number for the id value of interest. You can then select the rows that are 3 either side of the id value:

WITH CTE AS (
  SELECT Id, Name,
         ROW_NUMBER() OVER (ORDER BY Id) AS rn
  FROM names
),
CTE2 AS (
  SELECT rn AS IdRow
  FROM CTE
  WHERE Id = 11
)
SELECT Id, Name
FROM CTE
CROSS JOIN CTE2
WHERE rn BETWEEN IdRow - 3 AND IdRow + 3
  AND rn != IdRow

Output (for this example with Id = 11)

Id  Name
8   Aaran Mckeown
9   Riaz Stone
10  Conrad Mclean
12  Milli Clifford
13  Kajetan Barajas
14  Missy Malone

Demo (which also includes Id = 3) on dbfiddle

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 2006

Another shorter and simpler way:

select * from test where ((Id<11 and ID>(11-4)) or (Id>11 and ID<(11+4)))

Upvotes: 1

VBoka
VBoka

Reputation: 9083

Here the column you send is 2:

  select id, name
  from test
  where id in (ISNULL(2-1,0), ISNULL(2-2,0), ISNULL(2-3,0))
  or id in (ISNULL(2+1,0), ISNULL(2+2,0), ISNULL(2+3,0));

Another way is:

select id, name from (SELECT top 3 *
                      FROM test
                      WHERE id < 2
                      order by id desc) b
UNION
select id, name from (select *
                      from test
                      order by id
                      offset 2 rows
                      fetch next 3 rows only) a

Here is a DEMO for both examples.

Upvotes: 0

Related Questions