Jump to content
KillGorack

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

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

 

 

Share this post


Link to post
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.

Share this post


Link to post
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)

Share this post


Link to post
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>

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
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 = ?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.