CODEWITHSUNDEEP
mysqlsql-order-by
Nir
Nir

Reputation: 25369

Mysql - order by 2 ranks

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]?

Upvotes: 0

Views: 428

Answers (3)

Stefan Gehrig
Stefan Gehrig

Reputation: 83622

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.

Upvotes: 0

Brimstedt
Brimstedt

Reputation: 3140

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?

Upvotes: 2

Josh
Josh

Reputation: 6322

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

Upvotes: 0

Related Questions