Mysql - order by 2 ranks

Question

Hi,

I have a table of items. item has id, score 1, score 2

I want to select 12 known items (using WHERE id IN (....)) and order them by a score that is the sum of score1 and score2's ranks. rank is the location of the score if the 12 items were ordered by it.

How can I do this in mysql?

Edit:

After 4 answers I see that the solution is probably more complicated than I expected. so let me ask the question in another way.

if I use select *, [calculation] from table where id IN (odered list of ids). Can I somehow use the position of each id in the original ordered list of ids in [calculation]?

Answer 1

Is this what you are looking to do?

SELECT
(CAST(score1 AS INT) + CAST(score2 AS INT)) AS rank
FROM items
WHERE id IN(...)
ORDER BY rank DESC

Answer 2

Out of my head:

CREATE TEMPORARY TABLE t_1(score_1_rank INT AUTO_INCREMENT, id)
CREATE TEMPORARY TABLE t_2(score_2_rank INT AUTO_INCREMENT, id)

INSERT INTO t_1(id)
SELECT id
FROM items i
WHERE id IN () 
ORDER BY score_1

INSERT INTO t_2(id)
SELECT id
FROM items i
WHERE id IN ()
ORDER BY score_2

SELECT ...
FROM   items i
INNER JOIN t_1 t1
  ON t1.id = i.id
INNER JOIN t_2 t2
  ON t2.id = i.id
ORDER BY t1.rank + t2.rank

Did I understand your request correctly?

Answer 3

SELECT id, score1, score2, 
    r1.rank AS rank1, 
    r2.rank AS rank2, 
    (r1.rank + r2.rank) rankSum
FROM items i
INNER JOIN (
    SELECT @rank := @rank + 1 AS rank, id
    FROM items, (SELECT @rank :=0) AS r
    ORDER BY score1
) AS r1 ON r1.id = i.id
INNER JOIN (
    SELECT @rank := @rank + 1 AS rank, id
    FROM items, (SELECT @rank :=0) AS r
    ORDER BY score2
) AS r2 ON r2.id = i.id
ORDER BY rankSum

But I doubt that this will be somehow very efficient, not only because the query cannot make use of the query cache.

Answer 4

Don't do it in the Database. Collect the data with "where in" and then calculate the rank in the application and order it there. Might be easier to implement, faster (app layer usually does scalle better then the database) and easier to maintain.