Change query to obtain result similar to MSSQL RANK function

Question

I have modified the query as explained in Rank function in MySQL answered by @mukhesh_soni and @salman_A to obtain result similiar to MSSQL RANK() function and my query is as following:

SELECT id, rank_column, 
@curRank := IF(@prevVal=rank_column, @curRank, @studentNumber) AS rank, 
@studentNumber := @studentNumber + 1 as studentNumber, 
@prevVal:=rank_column
FROM rank_table, (
SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1
) r
ORDER BY rank_column

But the query also returns @studentNumber and @prevVal in result. How can I omit these two or I have to ignore them in PHP?

The answer given by salman_A is similar to MSSQL DENSE_RANK() where as I need RANK() only with omitting above two unnecessary columns in result.

You could simply wrap that into another SELECT statement, that selects only the columns you want from the “inner” SELECT … — CBroe, Aug 16 '14 at 14:21
If that is the only solution then result with select inside select will be far better than ignoring them in PHP in term of time taken to complete the request? — Rajan Sharma, Aug 16 '14 at 14:29
Doesn't ignoring them in PHP happen automatically? Your PHP will only access the columns you care about. — Barmar, Aug 16 '14 at 14:30
This would be sooo much easier with a database supporting modern SQL features. — , Aug 16 '14 at 14:53

Gordon Linoff · Answer 1 · 2014-08-16T18:25:02.823

Gosh, dense_rank() or row_number() would be easier. Let's see if we can do this. Getting rid of prevval is both easy and recommended:

SELECT id, rank_column, 
       @curRank := IF(@prevVal = rank_column,
                      @curRank,
                      if(@prevVal:=rank_column, @studentNumber, @studentNumber)
                     ) AS rank, 
       @studentNumber := @studentNumber + 1 as studentNumber
FROM rank_table CROSS JOIN
     (SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1) vars
ORDER BY rank_column ;

It is recommended because MySQL does not guarantee the order of evaluation of expressions in the select clause. The variable assignment can occur in any order. This can be fixed, but it makes for messier clauses. We really want only one expression with all the variables. You can do it like this:

SELECT id, rank_column, 
       @curRank := IF((@studentNumber := @studentNumber + 1) >= 0,
                      if(@prevVal = rank_column,
                         @curRank,
                         if(@prevVal:=rank_column, @studentNumber, @studentNumber)
                        ),
                      NULL) AS rank
FROM rank_table CROSS JOIN
     (SELECT @curRank := 0, @prevVal := null, @studentNumber := 0) vars
ORDER BY rank_column ;

Notice I changed the initial value to 0, because the value gets incremented before being used.

Thanks @gordon-linoff. First query has StudentNumber as output but it's okay and second query is assigning rank 1 to each row. — Rajan Sharma, Aug 16 '14 at 17:31
@RajanSharma . . . The problem with the second was operator precedence. — Gordon Linoff, Aug 16 '14 at 18:25

Change query to obtain result similar to MSSQL RANK function

1 Answers1