Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

oracle - Rank function in MySQL with Order By clause

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...