Rank function in MySQL with Order By clause

mysql, oracle, rank, window-functions

Solution

According to the link you gave it should look like this:

SELECT    a.*,
( 
            CASE a.field1 
            WHEN @curType 
            THEN @curRow := @curRow + 1 
            ELSE @curRow := 1 AND @curType := a.field1 END
          ) + 1 AS rank
FROM      table_a a,
          (SELECT @curRow := 0, @curType := '') r
ORDER BY  a.field1, a.field2 desc;

Here are 2 fiddles, one for oracle and one for mySql based on the example from the link you gave:

- oracle

- Mysql

Problem

How could this (Oracle) SQL: ``` select a.*, rank() over (partition by a.field1 order by a.field2 desc) field_rank from table_a a order by a.field1, a.field2 ``` be translated into MySQL? This question seems to be similar but there is no Order By in the end of the base query. Also, does it matter that it is ordered by the fields of partition?

Original source

Related problems