Previous, and next record (when NOT ordered by ID)

[KillGorack]

SQL is made up below, but I have something similar sorted by dates, and NOT by ID.

Currently I get this done by getting all the ID's in an array ordered by date and gleaning the two record ID's from that array.

if the table is huge, that might not be the best way.

Just seems like there should be an easier way to do it. Any ideas?

Select 
 startrek.ID,
 optc.opt_value as trk_series_id,
 optc.ID as trk_series_idID,
 startrek.trk_title,
 startrek.trk_episode,
 startrek.trk_season,
 startrek.trk_airdate,
 startrek.trk_stardate 
FROM startrek 
JOIN opt optc ON optc.ID = startrek.trk_series_id   
ORDER BY 
 trk_airdate ASC

 

 

[requinix]

Given that this is historical data and so not expected to change, I would assign each record a number according to the air date. Which could be the episode number. So you could just fetch the previous and next episodes according to the episode number.

[Barand]

I'd get the date difference (days) between your search date and the airdate and sort on the absolute value of this diff. The three smallest values will be current, previous and next.

I.E.

... ORDER BY ABS(DATEDIFF('$searchdate', trk_airdate)) LIMIT 3

or ... LIMIT 1,2 ( if you want to omit the current episode)

[KillGorack]

11 hours ago, requinix said:

Given that this is historical data and so not expected to change, I would assign each record a number according to the air date. Which could be the episode number. So you could just fetch the previous and next episodes according to the episode number.

I like this solution, but reordering the table isn't possible in my situation

 

4 hours ago, Barand said:

I'd get the date difference (days) between your search date and the airdate and sort on the absolute value of this diff. The three smallest values will be current, previous and next.

I.E.

... ORDER BY ABS(DATEDIFF('$searchdate', trk_airdate)) LIMIT 3

or ... LIMIT 1,2 ( if you want to omit the current episode)

Even in this example data we have overlapping dates, which could cause skipping records? I will test this to be sure.

 

<Off topic>

In the early 90's DS9, and TNG aired at the same time, Then later in the same decade Voyager, and DS9 overlapped.

</Off topic>

[maxxd]

I think the way you're doing it is not a bad solution.

Grab an array of the IDs (select only the ID in order to make it a quick query) at first page load on initial visit to the site and store that in session or a cookie - it's not like the historical air date is going to change, so the data doesn't need to be all that fresh, honestly. Then you can grab the next or previous episode by the episode ID from the previously set and stored array.

[Barand]

15 minutes ago, KillGorack said:

In the early 90's DS9, and TNG aired at the same time, Then later in the same decade Voyager, and DS9 overlapped.

The tell it which series you are searching

... WHERE trk_series_id = ?

[KillGorack]

I have to get my head around this but it seems SQL has some counting functionality.

The code below works, 113 is the current ID we're looking at and the sql below will give previous, and next as well.

From a coworker;

WITH numberlist AS (SELECT ID, row_number() OVER (ORDER BY trk_airdate ASC) as RN from startrek) 
SELECT numberlist.* 
FROM numberlist 
WHERE RN IN (SELECT RN + i 
FROM numberlist 
CROSS JOIN (SELECT -1 AS i UNION ALL SELECT 0 UNION ALL SELECT 1) n 
WHERE ID = 113) ORDER BY RN

wonderful