Difference between revisions of "RANK"

From SQLZOO
Jump to navigation Jump to search
 
(5 intermediate revisions by 2 users not shown)
Line 9: Line 9:
 
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
 
</table>
 
</table>
<h1>RANK</h1>
 
 
<p>RANK() OVER (ORDER BY f DESC) returns the rank position relative to the expression f. </p>
 
<p>RANK() OVER (ORDER BY f DESC) returns the rank position relative to the expression f. </p>
 
<pre style='width:60ex'>
 
<pre style='width:60ex'>
Line 15: Line 14:
 
</pre>
 
</pre>
  
<div class='qu'>
+
<div class='ht'>
In this example we show the ranking, by population of those countries with a population of over 180 million.  
+
In this example we show the ranking, by population of those countries with a population of over 180 million.
<source lang='sql' class='def'>
+
<source lang='sql' class='def e-oracle e-mssql'>
 
SELECT name,population,
 
SELECT name,population,
 
       RANK() OVER (ORDER BY population DESC)
 
       RANK() OVER (ORDER BY population DESC)
 
       AS r
 
       AS r
FROM bbc WHERE population>180000000
+
FROM world WHERE population>180000000
 
ORDER BY name
 
ORDER BY name
 +
</source>
 +
<source lang='sql' class='def'>
 
</source>
 
</source>
 
</div>
 
</div>
  
 +
==Using RANK OVER PARTITION==
 +
<div class='ht'>
 +
You can see view the RANK according to continent. This shows the biggest country
 +
<source lang='sql' class='def e-oracle e-mssql'>
 +
SELECT
 +
name,population,
 +
RANK() OVER (ORDER BY population DESC) AS world_rank,
 +
RANK() OVER (PARTITION BY continent ORDER BY population DESC)
 +
            AS local_rank
 +
FROM world WHERE population>100000000
 +
ORDER BY name
 +
</source>
 +
</div>
 
<p>See also</p>
 
<p>See also</p>

Latest revision as of 08:42, 5 July 2017

Compatibility
RANK() OVER (ORDER BY f DESC)
EngineOKAlternative
ingresNo
mysqlNo
oracleYes
postgresNo
sqlserverYes

RANK() OVER (ORDER BY f DESC) returns the rank position relative to the expression f.

  RANK() OVER (ORDER BY f DESC) 

In this example we show the ranking, by population of those countries with a population of over 180 million.

SELECT name,population,
       RANK() OVER (ORDER BY population DESC)
       AS r
FROM world WHERE population>180000000
ORDER BY name

Using RANK OVER PARTITION

You can see view the RANK according to continent. This shows the biggest country

SELECT
 name,population,
 RANK() OVER (ORDER BY population DESC) AS world_rank,
 RANK() OVER (PARTITION BY continent ORDER BY population DESC) 
            AS local_rank
FROM world WHERE population>100000000
ORDER BY name

See also