How to convert this query from MySQL to SQL Server?

Question

Here is the source table:

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

Here is the result table:

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

I have the MySQL version query, how to convert it to SQL server version? I tried to do the declare but I have no idea how to update the value of the variables.

SELECT Score, ranking AS Rank
FROM
(
SELECT 
    Score,
    CASE
        WHEN @dummy = Score 
        THEN @ranking := @ranking
        ELSE @ranking := @ranking + 1
    END as ranking,
    @dummy := Score
FROM Scores, (SELECT @ranking := 0, @dummy := -1) init 
ORDER BY Score DESC
)AS Result

Use DENSE_RANK. learn.microsoft.com/en-us/sql/t-sql/functions/… — Sean Lange
– Sean Lange, Commented Jun 6, 2017 at 18:34
I know the other versions of the solution, I just want to know how to convert this into the SQL server version. Especially how to use the variables, update the variables in the SQL server. — Jian Zhang
– Jian Zhang, Commented Jun 6, 2017 at 18:37
What do you mean you know the other versions of the solution? If you know how to do this why are you asking? You don't need any variables for this. — Sean Lange
– Sean Lange, Commented Jun 6, 2017 at 18:40
Yes, I can implement this without using the variables, but I just want to know how to use the variables to implement this in the SQL server, just like the code in the MySQL. — Jian Zhang
– Jian Zhang, Commented Jun 6, 2017 at 18:45

Gordon Linoff · Accepted Answer · 2017-06-07 02:03:48Z

4

Your code is a MySQL work-around for the ANSI standard DENSE_RANK() function (as explained by Sean Lange in a comment). The code simply looks like:

SELECT s.*, DENSE_RANK() OVER (ORDER BY Score DESC) as rank
FROM Scores s
ORDER BY Score DESC;

Incidentally, the MySQL code itself is not really accurate. The following is much safer:

SELECT Score, ranking AS Rank
FROM (SELECT Score,
             (@rn := if(@dummy = Score, @rn,
                             if(@dummy := Score, @rn + 1, @rn + 1)
                            )
             ) as ranking
      FROM Scores CROSS JOIN
           (SELECT @rn := 0, @dummy := -1) init 
      ORDER BY Score DESC
     ) s;

The key difference is that all the assignments to variables occur in a single expression. MySQL does not guarantee the order of evaluations of expressions in a SELECT, so you should not use @dummy in one expression and then assign it in another.

edited Jun 7, 2017 at 2:03

answered Jun 6, 2017 at 18:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jian Zhang Over a year ago

Thanks for the reply and the improvement. So, for the SQL-Server, we can only use the DENSE_RANK() function? Can we implement the DENSE_RANK() by ourselves?

Jian Zhang Over a year ago

By the way, I tested your code, it is incorrect. All the rankings are NULL.

S3S Over a year ago

No @JianZhang, you are wrong. Gordon's code is correct. You can see at this link. Comical that you're so quick to claim someone is wrong after they critiqued your original code as well.

Bacon Bits · Accepted Answer · 2017-06-06 18:58:56Z

1

SQL Server doesn't support variables used in that manner, so the syntax doesn't translate. Scalar variables are set once by a query, not set once for each row of the result set. MySQL's syntax here is a hack to get analytic-function-like behavior without analytic function support. You should just use:

SELECT Score,
    DENSE_RANK() OVER(ORDER BY Score DESC) AS Rank
FROM Scores
ORDER BY Score DESC;

If you insist on not using DENSE_RANK(), you can use the SQL Server syntax from SQL Server 2000:

SELECT s1.Score,
    (SELECT COUNT(DISTINCT s2.Score) FROM Scores s2 WHERE s1.Score <= s2.Score) AS Ranking
FROM Scores s1
ORDER BY s1.Score DESC;

answered Jun 6, 2017 at 18:58

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

1 Comment

Jian Zhang Over a year ago

Thank you. "SQL Server doesn't support variables used in that manner, so the syntax doesn't translate." This is what I need~

Collectives™ on Stack Overflow

How to convert this query from MySQL to SQL Server?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related