CODEWITHSUNDEEP
mysql
ajdi
ajdi

Reputation: 454

mySQL - Find item before and after matching query

I'm working on a piece of software that tracks the play count of a couple of radio stations. Yay stats and reporting!

Everything is logging fine, but reporting is where I'm having a few curly requests thrown at me.

Each track is logged in a table called playhistory - create syntax below.

CREATE TABLE `playhistory` (
  `id` int(255) unsigned NOT NULL AUTO_INCREMENT,
  `time` int(255) NOT NULL,
  `station` int(255) DEFAULT NULL,
  `artist` varchar(255) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `track_hash` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1186717 DEFAULT CHARSET=latin1;

An few example rows would be like the below;

id: 123
time: 1461298065
station: 2
title: Stressed Out
artist: Twenty One Pilots
track_hash: d52f6310b1b27d1bef8663dec06fcb80
--
id: 125
time: 1461298069
station: 1
title: Original Sin
artist: INXS
track_hash: 3cd1d4d993a26965a952202a4a957181
--
id: 124
time: 1461298365
station: 2
title: I knew you were trouble
artist: Taylor Swift
track_hash: 42aadeff0ce10bbfb4ebc91cf58e9802

What I'm trying to do is find out for each occurrence of the song with track_hash of d52f6310b1b27d1bef8663dec06fcb80, what is the next item in the table, but only when station is equal to 2.

Ideally, I'll end up with a set of results that tell me what the next played song after "Stressed Out" is each time it was played. In the example set below, I'd see the row for 'I knew you were trouble' AKA id=24 returned.

Upvotes: 1

Views: 51

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

SELECT t1.id, t1.title, t1.artist
FROM playhistory t1
INNER JOIN
(
    SELECT id, station
    FROM playhistory
    WHERE track_hash = 'd52f6310b1b27d1bef8663dec06fcb80'
) t2
    ON t1.id = t2.id + 1 AND t2.station = 2

Follow the link below for a running demo:

SQLFiddle

Upvotes: 2

Related Questions