CODEWITHSUNDEEP
phpmysqlratingrating-system
Joris Blanc
Joris Blanc

Reputation: 601

MYSQL sort the next row with the UploadDate when Wilson score interval are equal

I'm working on my photo gallery and I have a filter to sort my media with a Wilson score interval. This score is saved in a field of my table called score

For this query I use the following fields : id, UploadTime, score

The issue I am having is that some scores will be exactly the same if lets say if mulitple picture have only one thumb up. In that case I would like to sort the media by the UploadDate field, make the older picture less relevant.

This is my script to sort out all my picture (keep in mind that the score must me higher than 0 to get in the list) 

SELECT *
FROM photos
WHERE   status = 0 
    AND score > 0 
ORDER BY score DESC, id DESC

Then for getting the next picture (the next picture is a picture with a lower score or if score is equal output the latest one uploaded). And the $id is the unique id of the current picture

SELECT   *
FROM     photos p
INNER JOIN
    (
    SELECT score, id
    FROM photos
    WHERE id = ".$id."
    ) p2 ON p.score < p2.score

WHERE p.status = 0 AND p.net_votes > 0
ORDER BY p.score DESC, p.UploadTime DESC
LIMIT    1

I have used up all my ideas on how to do this.

Upvotes: 1

Views: 162

Answers (1)

Adi
Adi

Reputation: 5179

I'm not entirly sure (I might have gotten a > instead of < somewhere). Hopefully this will work 

SELECT *
FROM   photos
WHERE  status = 0
       AND net_votes > 0
       AND ( score < (SELECT p1.score
                      FROM   photos p1
                      WHERE  p1.id = :id)
              OR uploadtime < (SELECT p2.uploadtime
                                  FROM   photos p2
                                  WHERE  p2.id = :id) )
ORDER  BY score DESC,
          uploadtime DESC
LIMIT  1

Upvotes: 1

Related Questions