Get previous and next records based on id from result set

• PiMané

  SSChampion

  Points: 13965

  More actions

  March 3, 2016 at 10:28 am

  #325334

  Hi,

  I've a procedure that lists entities with a certain filter given by the application.

  The result set is an entities' list with the Id and other fields. The app stores just the filter and when an entity is opened it also stores it's id.

  The purpose is to have a Next and Previous navigation on the entity data but since we only have the id and the filter I'd like to know if there's an easy and fast way of "filtering" the list with the id and get the next and previous record id...

  List example:

  SELECT id, name, phone, address FROM entities WHERE phone LIKE @phone AND idCountry = @idCountry ORDER BY name OFFSET ((@pageNumber - 1) * @numRecords) ROWS FETCH NEXT @numRecords ROWS ONLY

  The navigation example would be something like:

  SELECT id, [...] previousId, [...] nextId FROM entities WHERE phone LIKE @phone AND idCountry = @idCountry AND id = @id ORDER BY name

  The LAG and LEAD should do the work but since I'm filtering the id the result set only has one record they return NULL...

  If we had full "control" of the application the list could return the record position and I would search the position - 1 and + 1 but we can't control the classes...

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 3, 2016 at 11:27 am

  #1862861

  Use a CTE or derived table to generate the LEAD and LAG before filtering.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738347

  More actions

  March 3, 2016 at 11:31 am

  #1862863

  Luis Cazares (3/3/2016)

  ---

  Use a CTE or derived table to generate the LEAD and LAG before filtering.

  +1

• PiMané

  SSChampion

  Points: 13965

  More actions

  March 3, 2016 at 11:36 am

  #1862867

  I used the following code but its slow...

  WITH data AS (

  SELECT id, name, phone, address, ROW_NUMBER() OVER (ORDER BY name) row FROM entities WHERE phone LIKE @phone AND idCountry = @idCountry

  ), rowPos AS (

  SELECT row FROM data WHERE id = @id

  )

  SELECT * FROM data WHERE (SELECT row FROM rowPos) BETWEEN data.row - 1 AND data.row + 1 ORDER BY data.row

  It works fine but it's a bit slow...

  ---

  
  If you need to work better, try working less...

• PiMané

  SSChampion

  Points: 13965

  More actions

  March 3, 2016 at 11:38 am

  #1862870

  Steve Jones - SSC Editor (3/3/2016)

  ---

  Luis Cazares (3/3/2016)

  ---

  Use a CTE or derived table to generate the LEAD and LAG before filtering.

  +1

  Do you mean:

  WITH data AS (

  SELECT LAG(id) OVER (ORDER BY name) previousId, LEAD(id) OVER (ORDER BY name) nextId, id FROM entities WHERE phone LIKE @phone AND idCountry = @idCountry

  )

  SELECT * FROM data id = @id

  ---

  
  If you need to work better, try working less...