Jump to content

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


KillGorack

Recommended Posts

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

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.