9

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user1433877
  • 145
  • 2
  • 8
  • i didnot know qracle so can you show your desired result and sample data first – Ankit Sharma Jun 04 '12 at 13:35
  • I can give a small explanation, it is something like you have column A and B and you order by A then B and you give a rank number based on B for each member of groups in A. If I'm correct. – user1433877 Jun 04 '12 at 14:25

1 Answers1

16

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:

  1. oracle
  2. Mysql
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • 1
    Nifty solution for the partition by part. But I wonder if MySQL will ever arrive in the 21st century and catch up on modern SQL features. –  Jun 04 '12 at 13:59
  • 2
    +1 Nice! Thanks, it works! Only thing I changed was that I didn't write "+ 1" after the expression (it started from 2 then). – user1433877 Jun 04 '12 at 14:20