CODEWITHSUNDEEP
mysqlsql
yogesh
yogesh

Reputation: 29

Why below query not giving proper output

I have two table topic and question_set. I want to show top 3 question from each topic, I have written the query but, it's not generating rank, It's giving same rank for each row.

DROP TEMPORARY TABLE IF EXISTS temp_table ;
 CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS (   
 SELECT A.ID, B.topic_name 
    FROM question_sets A 
    INNER JOIN topics B 
    ON A.topic_id = B.Id 
    WHERE  test_section_id = 3 
    AND exam_category_id = 2 
    ORDER BY appeared 
 );

SELECT ID,topic_name , 
       @rank := IF(@topic = topic_name, @rank + 1, 1) AS ranking,
       @topic := topic_name 
FROM temp_table
ORDER BY topic_name DESC

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

In more recent versions of MySQL, you need to sort the data in a subquery before using variables. And, you should not assign a variable in one expression and use it in another.

So:

SELECT ID,topic_name , 
       (@rank := IF(@topic = topic_name, @rank + 1,
                    IF(@topic := topic_name, 1, 1)
                   )
       ) as ranking
FROM (SELECT tt.*
      FROM temp_table tt
      ORDER BY topic_name DESC
     ) tt CROSS JOIN
     (SELECT @topic := '', @rank := 0) params;

You probably want a second key after the topic_name DESC if you actually want to rank by some column. You cannot depend on the ordering in the temporary table -- because tables represent unordered sets.

In MySQL 8+, you would simply do:

select tt.*,
       row_number() over (partition by topic_name order by ?) as seqnum
from temp_table tt;

Upvotes: 1

Related Questions