0

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

1 Answer 1

0
select s2.*, @nv NXV, (@nv := id) NNXV from (
select s1.*,
@pv PRV, (@pv := id) NPRV, (@rn := @rn + 1) RN
from (
SELECT DISTINCT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo,
profiles.rnd_order
FROM
`profiles`
LEFT JOIN profile_photos
ON (
  profiles.id = profile_photos.member_id
  AND profile_photos.photo_status = 1
), (select @rn := 0)  RNT
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) s1
) s2
order by RN desc;

see http://sqlfiddle.com/#!9/409b3/20

you will find the info you wanted in columns NXV and PRV

This fiddle http://sqlfiddle.com/#!9/409b3/47 shows the query for a specific profile id.

select p.*, pf.*,
       pmax.id, pmax.rnd_order,
       pmin.id, pmin.rnd_order
  from profiles p left outer join
       profile_photos pf
       on  pf.member_id = p.id
       and pf.photo_status = 1,
       profiles pmax,
       profiles pmin
  where p.id = 100024
    and pmax.rnd_order = (select ifnull(max(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order < p.rnd_order)
    and pmin.rnd_order = (select ifnull(min(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order > p.rnd_order)
;

Can you check this for performance? (explain plan looks promising)

2
  • @Gerhard H. Pille I have a web app that lists records from the main query, then a user can open a single record inside this I wan't show the previous and next records, therefore I have above two queries for Previous and Next means I need one or two queries where I can give as Input the actual profile.id and that will give me back the previous and next record. Regarding sort order I have changed all from desc to asc but had not luck to got first all records with photo_status = 1.
    – nenad007
    Commented Feb 16, 2018 at 9:33
  • Awesome! Thank you very much it's working correctly in every case and it uses all Indexes.
    – nenad007
    Commented Feb 16, 2018 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.