LeetCode SQL

178. Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
For example, given the above Scores table, your query should generate the following report (order by highest score):+-------+---------+
| score | Rank |
+-------+---------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+---------+
Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example `Rank`.

Logic: Use two copies for the scores tables

Solution:

# Write your MySQL query statement belowselect a.score Score,(select count(distinct score)+1
from scores b
where a.score < b.score) `Rank`
from scores a
order by a.score desc

--

--

--

My homepage to record my thought processes for solving SQL and Algorithm questions

Recommended from Medium

Useful Chrome Extensions for Frontend Developers

Improving Apache HTTP Server’s performance using ListenCoresBucketsRatio directive

Odoo 12 Migration: from Ubuntu to Centos.

OpenSquat | Detection Of Phishing Domains And Domain Squatting

Stylised ListPopupWindow in Android

Amazon Buy Engineering Economics and Costing Festival Discount Offer

Docs Italia, simpler documents for a simpler Public Administration

Grillo’s earthquake early-warning system is now open source

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Isabelle

Isabelle

In love with telling stories with data

More from Medium

Leetcode SQL

LeetCode SQL| 182. Duplicate Emails

Normalization in SQL

SQL COMMANDS AND NORMALIZATION