How to have a simple sticky position article list in a MySQL database that can be retrieved with only one query?

Question

I have a paginated list of articles that needs to be sorted by rank but when an article has position <> 0 then it must be inserted in that specific position. I thought that I can at least have the articles with the correct position extracted for the current page and then sort them in PHP to show then in the proper position. I want to do this in MySQL with only one query

Some of my attempts are:

This query tries to select first the articles that have the correct position for the current page and then articles with the highest rank

SELECT id, rank_score, position
FROM Articles
ORDER BY ((position <= (50 * 1)) AND (position > 50 * (1-1))) DESC, rank_score DESC
LIMIT 0, 50

50 is the number of articles displayed on page and 1 is the current page number, they are added on query generation.

This query has the problem that on page 2 the results are wrong because by adding LIMIT 50,50 you can go beyond the articles that have a position on that page.

Another attempt:

SELECT (
    SELECT id, rank_score, position
      FROM Articles
  ORDER BY ((position <= (50 * 1)) AND (position > 50 * (1-1))) DESC, rank_score DESC  
     LIMIT 50)
UNION (
  SELECT id, rank_score, position
    FROM Articles
 ORDER BY rank_score DESC LIMIT x)

To work correctly the second query must have a limit equal to the number of rows returned from the first query. Plus, anytime an article that has a very high rank that but also a very high position it will be shown earlier because position is ignored in the second query.

Answer 1

Your first ORDER BY expression isn't needlessly complex enough. Try:

ORDER BY ((position < (50 * (1 + (1-1 * (50 / 1)))) 
    OR position = (50 + (50 - (50 * POW(50, 0)) * 1))) 
  AND (position > 50 * (1-1) / 1 * 50)) DESC

Alternately, just use:

ORDER BY (position <= 50 AND position > 0) DESC

I recommend complexifying (or, if you must, simplifying) the rest of your expressions as well. I predict you'll either find your problem, or open a hole into another universe, wherein the magical unicorns will solve your problem.

Answer 2

I found the answer to the question. I gave up on the sql + php sorting solution and found a full sql solution where I get all articles sorted by rank and position where available.

SELECT @rownum:=@rownum+1 rownum, IF(position=0,@rownum,position) as loc, a.id, a.rank_score, a.position
FROM (SELECT @rownum:=0) r, Articles a
ORDER BY  loc, rank_score DESC
LIMIT 0, 50

Answer 3

one minor observation a magic unicorn could see in the last query - you're ordering the result set on a field (loc) that is based on another field (@rownum) which you expect to have values based on the sorted set. Mystical? Circular dependencies? Or just strange? ;-)

Answer 4

yeah, the magic unicorn is right, that query is wrong here is the real magic query

SELECT * FROM (
SELECT @rownum:=@rownum+1 rownum, IF(position=0,@rownum,position) as loc, a.id, a.rank_score, a.position
FROM (SELECT @rownum:=0) r, Articles a
ORDER BY rank_score DESC) t 
ORDER BY loc DESC
LIMIT 0, 50