I have a SQL Query with two ORDER BY criterias. How I can get the correct previous and next position for every record?
My Solution works as long the actual and previous or next record match the same first ORDER BY criteria.
Sample data
http://sqlfiddle.com/#!9/409b3/3
Target: How I can get for id 100026 the previous id 100018 and the next id 100003?
Schema
CREATE TABLE `profiles` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`rnd_order` INT(11) NOT NULL,
`profile_status` INT(11) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `rnd_order` (`rnd_order`),
KEY `profile_status` (`profile_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `profile_photos` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`member_id` INT(11) DEFAULT NULL,
`photo` VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`photo_status` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `member_id` (`member_id`),
KEY `photo_status` (`photo_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Select query
SELECT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
`profiles`
LEFT JOIN profile_photos
ON (
profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
)
WHERE (profile_photos.photo_status = 1 OR
profile_photos.photo_status is null)
GROUP BY profiles.id
ORDER BY photo_status DESC, profiles.rnd_order DESC;
My Solution (it don't work for the case where order by criteria of previous or next record is different)
Previous
SELECT profile.id,,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
`profiles`
JOIN profile_photos
ON (
profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
)
AND (profile_photos.photo_status = 1 AND profiles.rnd_order < {actual_profile_id})
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order ASC
LIMIT 1
Next
SELECT profile.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
`profiles`
JOIN profile_photos
ON (
profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
)
AND ((profile_photos.photo_status = 1 AND profiles.rnd_order > {actual_profile_id}) OR (profile_photos.photo_status IS NULL))
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order DESC
LIMIT 1