Display equal ranking if count is the same

Question

I am trying to figure out how to display ranking correctly where if multiple authors have the same article count then they get the same ranking and then the ranking resume at the correct spot. For example, if the the first 4 have the same article count then i want them to all rank as 1 and then for the 5th person to rank as 5. The following query outputs as shown below:

SELECT @rownum:=@rownum + 1 as Rank, 
   subq1.*
FROM ( SELECT Uni.University_Name, COUNT( * ) AS ArticleCount
FROM Author_University AS AU, University AS Uni, Articles AS Art
WHERE Art.Year_Published >= '2010'
AND Uni.University_ID = AU.University_ID
AND Art.Article_ID = AU.Article_ID
GROUP BY University_Name
ORDER BY ArticleCount DESC
LIMIT 0 , 25 
  ) subq1,
(SELECT @rownum := 0) r

Answer 1

Here is one way to achieve equal ranking with variables:

SELECT
  @lastrank := (subq1.ArticleCount = @lastcount) * @lastrank
            + (subq1.ArticleCount <> @lastcount) * (@rank := @rank + 1) AS Rank,
  subq1.University_Name,
  @lastcount := subq1.ArticleCount AS ArticleCount
FROM
  (
    SELECT
      Uni.University_Name,
      COUNT(*) AS ArticleCount
    FROM
      Author_University AS AU,
      University AS Uni,
      Articles AS Art
    WHERE
      Art.Year_Published >= '2010'
      AND Uni.University_ID = AU.University_ID
      AND Art.Article_ID = AU.Article_ID
    GROUP BY
      University_Name
    ORDER BY
      ArticleCount DESC
    LIMIT
      0, 25 
  ) AS subq1,
  (
    SELECT
      @lastcount := 0,
      @lastrank := (@rank := 0)
  ) AS r
;

The purpose of the three variables in this method is:

• @rank – to calculate and store sequential, unique ranking;

• @lastrank – to store the rank of the previous row;

• @lastcount – to store the count value of the previous row.

Here is how the Rank expression works. If the current row's ArticleCount is equal to @lastcount, the ArticleCount = @lastcount condition evaluates to true and the other, ArticleCount <> @lastcount, of course, to false. In the context of an arithmetical operation (*), true is implicitly converted to 1 and false to 0. So, the expression becomes equivalent to this:

1 * @lastrank + 0 * (@rank := @rank + 1)

which, in turn, simplifies to just @lastrank. So, when ArticleCount is equal to @lastcount, @lastrank retains its value from the previous row. The current count is then stored in @lastcount to be compared against on the next row.

That way @lastrank stays the same until ArticleCount is no longer equal to @lastcount. At that point true and false – and, consequently, 1 and 0 – trade places and the expression becomes equivalent to this:

0 * @lastrank + 1 * (@rank := @rank + 1)

and so in the end @lastrank gets the result of evaluation of (@rank := @rank + 1), which will simply be a new ranking. Note, however, that (@rank := @rank + 1) actually evaluates on every row but assigned to @lastrank only when ArticleCount changes to a new value.

And it is the fact @rank increases on every row that allows us to get this kind of ranking, with gaps. For comparison, consider this CASE expression:

CASE WHEN ArticleCount = @lastcount THEN @lastrank ELSE (@rank := @rank + 1) END

or its equivalent using the IF() function:

IF(ArticleCount = @lastcount, @lastrank, (@rank := @rank + 1))

Each of these variations would give us equal ranking as well, but the ranking would be without gaps – that is, instead of e.g. 1, 1, 3, 4, 4, 4, 7 we would be getting 1, 1, 2, 3, 3, 3, 4. The reason is, the (@rank := @rank + 1) assignment in each case would take place only if/when Article differed from @lastcount – in other words, @rank would both increase and be assigned to @lastrank only when ArticleCount changed, unlike the first method, where @rank increases constantly.