Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Rank function in MySQL?
The rank() function can be used to give a rank for every row within the partition of a result set.
First, let us create a table −
mysql> create table RankDemo mysql> ( mysql> id int mysql> ); Query OK, 0 rows affected (0.53 sec)
Inserting records into table.
mysql> insert into RankDemo values(1); Query OK, 1 row affected (0.19 sec) mysql> insert into RankDemo values(3); Query OK, 1 row affected (0.12 sec) mysql> insert into RankDemo values(3); Query OK, 1 row affected (0.11 sec) mysql> insert into RankDemo values(4); Query OK, 1 row affected (0.12 sec) mysql> insert into RankDemo values(5); Query OK, 1 row affected (0.17 sec)
Displaying all records from the table with the help of select statement. The query is as follows −
mysql> select *from RankDemo;
The following is the output.
+------+ | id | +------+ | 1 | | 3 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)
Let us now apply the rank() function as I have discussed above.
mysql> SELECT mysql> id,RANK() OVER (ORDER BY id ) Ranking mysql> from RankDemo;
The following is the output that displays the rank.
+------+---------+ | id | Ranking | +------+---------+ | 1 | 1 | | 3 | 2 | | 3 | 2 | | 4 | 4 | | 5 | 5 | +------+---------+ 5 rows in set (0.04 sec)
Updated on: 30-Jul-2019
526 Views
Advertisements