Difference between revisions of "RANK"
Jump to navigation
Jump to search
| (2 intermediate revisions by the same user 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> | ||
<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 29: | Line 28: | ||
==Using RANK OVER PARTITION== | ==Using RANK OVER PARTITION== | ||
<div class='ht'> | |||
You can see view the RANK according to continent. This shows the biggest country | You can see view the RANK according to continent. This shows the biggest country | ||
<source lang='sql' class='def e-oracle e-mssql'> | <source lang='sql' class='def e-oracle e-mssql'> | ||
SELECT name,population, | 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 | FROM world WHERE population>100000000 | ||
ORDER BY name | ORDER BY name | ||
</source> | </source> | ||
</div> | |||
<p>See also</p> | <p>See also</p> | ||
Latest revision as of 08:42, 5 July 2017
| RANK() OVER (ORDER BY f DESC) | ||
|---|---|---|
| Engine | OK | Alternative |
| ingres | No | |
| mysql | No | |
| oracle | Yes | |
| postgres | No | |
| sqlserver | Yes | |
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