在MySQL记录中生成范围

Question

I read through other similar questions before, but none seems to have answered my question. Apologies if this is a repeat of something else, but I couldn't seem to find this question answered elsewhere.

I've got a database table that looks more or less like this:

ID | URL | Title | Episode | Sequence | Created_at | Modified_at

These are used to assign specific links to episodes of podcasts, and they're displayed in the order illustrated by the Sequence. I've built a Laravel framework that interacts with the table, and I'm able to change the order of things by manipulating the sequence, but I'm having trouble removing things and maintaining the sequence for the purposes that I want it. Say I have the following four things in the table:

id = 5, sequence = 1
id = 6, sequence = 3
id = 7, sequence = 2
id = 8, sequence = 4

When viewing the site, the page would list them in id order 5, 7, 6, 8. However, if I realize that there's been a problem with one of the links, I would remove it, at which point I might be left with ids 5, 7, 8. The sequence would be 1, 2, 4.

What I'm looking for at the moment is a way of "reseeding" the sequence column on a per-episode basis. It'd be something that basically takes all rows with a specific episode, sorts them in ascending order on sequence, and then "renumbers" them in ascending order. The sequence = 1, 2, 4, 5, 7, 8, 9 would change to 1, 2, 3, 4, 5, 6, 7.

Answer 1

It should be a relatively simple query such as

select
      YT.*
   from
      YourTable YT
   where
      YT.Episode = 'MO50'
   order by
      YT.Sequence

Now, if your sequences are creating gaps for whatever reason you could always implement a mySql variable to increment regardless of the ACTUAL sequence.. Try..

select
      @tmpRow := @tmpRow +1 as NewSequence,
      YT.*
   from
      YourTable YT,
      ( select @tmpRow := 0 ) sqlvars
   where
      YT.Episode = 'MO50'
   order by
      YT.Sequence

So, even if your actual sequences for a given show "MO50" are 2, 3, 8, 12, 15. The actual column "NewSequence" would show as 1, 2, 3, 4, 5.

Now, that said, you would STILL have the original "ID" column as the basis of editing any details and or remove episodes as needed... Does this help you?