Algorithm suggestion needed: What's the best way to sort TV show episodes?

I've got a Sqlite database with various TV show episodes, and I'd like to sort them ascending and descending by their "freshness", i.e. their season and episode number.

For example:

• Season 1开发者_如何学运维 Episode 1
• Season 1 Episode 2
• [...]
• Season 2 Episode 1

... or the other way around:

• Season 2 Episode 2
• Season 2 Episode 1
• [...]
• Season 1 Episode 2

I've got the season and episode numbers stored as two different variables for each entry in the database, so it's possible to do something based on that.

Do you have any suggestions as to how it can be done in the most optimal way possible?

Are you just asking for a sql statement? In that case, given your mention of 2 seperate variables, let's call them 'season' and episode respectively, you could do this:

ascending: select * from shows order by season asc, episode asc

descending: select * from shows order by season desc, episode desc

'most optimal', from a performance standpoint at least, would be to combine the 2 variables into a third (you can keep the 2 orginals for other stuff if you need to). Call that 'episodeOrder'.

'episodeOrder' has to sort first on season than on episode. ASsuming seasons will never have more than 100 episodes you could combine them as follows: 'episodeOrder' = xxyy where 'xx' is the seasonnr and yy is the episodenr, e.g season 2 episode 3 will become: 0203.

now you could sort by episodeError: select * from shows order by episodeError asc;

hth Geert-Jan