CODEWITHSUNDEEP
mysql
RekKA
RekKA

Reputation: 150

Getting incorrect rank using WHERE clause in mySQL

http://sqlfiddle.com/#!9/083f5d/1

It's returning 6th rank instead of 1st rank in above SQL query. What changes I need to do in SQL query so it provides 1st rank because I have used WHERE condition to check mpkid and roundpkid (WHERE mpkid=37 AND roundpkid=3).

Here is the query:

SELECT mpkid, totalvote, rank 
FROM ( 
 SELECT mpkid, roundpkid, totalvote, 
 @n := IF(@g = totalvote, @n, @n + 1) rank, 
 @g := totalvote 
 FROM tr_msearch_vote_summary, 
 (SELECT @n := 0) i 
 ORDER BY totalvote DESC 
) q 
WHERE mpkid=37 AND roundpkid=3

What I want:

Please see sqlfiddle first. There is only one record for round #3, and it is 37 so I want that it should display rank #1 for mpkid #37 and round #3 but it is giving rank #6.

Upvotes: 1

Views: 146

Answers (2)

Arth
Arth

Reputation: 13110

You are applying the WHERE condition after the ranking is already complete. I'm guessing you want to do the ranking after the WHERE is applied:

  SELECT t.mpkid, t.roundpkid,  
         @n := IF(@g = t.totalvote, @n, @n + 1) rank,
         @g := t.totalvote totalvote
    FROM tr_msearch_vote_summary t, (SELECT @n := 0) i 
   WHERE t.mpkid=37 AND t.roundpkid=3
ORDER BY t.totalvote DESC

You should also initialise @g to make sure it isn't preset in another query:

  SELECT t.mpkid, t.roundpkid,  
         @n := IF(@g = t.totalvote, @n, @n + 1) rank,
         @g := t.totalvote totalvote
    FROM tr_msearch_vote_summary t, (SELECT @n := 0, @g := NULL) i 
   WHERE t.mpkid=37 AND t.roundpkid=3
ORDER BY t.totalvote DESC

UPDATE

If you want the ranking grouped by round and then to simply SELECT by mpkid, this is a more powerful query:

SELECT mpkid,
       roundpkid,
       totalvote,
       rank
  FROM (
    SELECT t.mpkid,  
           @n := CASE 
                   WHEN @r = t.roundpkid AND @g = t.totalvote THEN @n
                   WHEN @r = t.roundpkid THEN @n + 1
                   ELSE 1
                 END rank,
           @r := t.roundpkid roundpkid,
           @g := t.totalvote totalvote
      FROM tr_msearch_vote_summary t, (SELECT @n := 0, @g := NULL, @r := NULL) i 
  ORDER BY t.roundpkid, t.totalvote DESC
       ) r
 WHERE mpkid = 37;

Note that you do not need to supply the roundpkid. See updated fiddle

Upvotes: 2

K Ahir
K Ahir

Reputation: 395

Please try this query,

SELECT mpkid, totalvote, rank 
FROM ( 
  SELECT mpkid, roundpkid, totalvote, 
  @n := IF(@g = totalvote, @n, @n + 1) rank, 
  @g := totalvote 
  FROM tr_msearch_vote_summary, 
  (SELECT @n := 0) i 
WHERE roundpkid=3
ORDER BY totalvote DESC 
) q 
WHERE mpkid=37

And here is your updated sqlfiddle. http://sqlfiddle.com/#!9/083f5d/9

Upvotes: 1

Related Questions