Contents,Table of Contents, Page Contents
How to rank in SQL
This tutorial shows you how to rank in SQL your data. We will first understand what is ranking function in SQL are then dense_rank SQL. Then also understand how to use row_number in SQL. We will first create SQL table and then insert our data to understand these three functions in SQL i.e. rank function SQL, oracle dense_rank, oracle row_number.
Create table in SQL
First, we will create table SQL emp_name which will have 8 columns, and the first column has a primary key. To create a table in SQL we will use CREATE TABLE statement. We have mentioned Query SQL create table as below. You can read more on how to create SQL table from this link.
CREATE TABLE emp_name ( empno NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) );
Describe function in SQL
After creating the table use describe function in SQL to check if SQL table was created correctly. Check the below image to understand it.
INSERT in SQL
INSERT INTO emp_name VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp_name VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp_name VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp_name VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp_name VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp_name VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp_name VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp_name VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp_name VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp_name VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp_name VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp_name VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp_name VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp_name VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
After INSERT SQL statement check data by using SQL SELECT statement.
SQL RANK over
Let’s first understand rank over SQL. How it works and its SQL syntax.
RANK() OVER ([ query_partition_clause ] order_by_clause)
In the above oracle query, we used first RANK() to tell that we are ranking the data then OVER with query_partition_clause means by which columns partition by SQL will be done. And in the last order_by_clause which will do ascending order or descending order by which column.
SELECT empno, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank FROM emp_name;
Now in the final rank SQL we can see we partitioned our data by deptno and order by sal. It means SQL column data will be arranged by deptno first then sal column data will be rank in SQL. we have not provided anything for ORDER BY which means it will be arranged in ascending order. Now see the final result of this SQL query in the below image.
In the above image, we can see that column DEPTNO is arranged first and the values in SAL ranked after that. One important point to be noted here is for DEPTNO 30 the salary of 1250 ranked twice as 2 but after that rank value 3 got skipped followed by 4. So we can conclude that if multiple no. repeats then SQL rank will remain same for them and after that new values will number of repetition plus number before repetition.
Now it’s time to understand dense_rank oracle and how it works. But before that let’s understand its SQL syntax.
DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)
we can see that the SQL syntax for dense_rank is the same as SQL syntax for oracle SQL rank. Then what is difference between rank and dense_rank? To know that let’s understand it by running the below SQL query.
SELECT empno, deptno, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS mydenserank FROM emp_name;
In the final result, we can see it works like SQL ranking function but instead of skipping repeated numbers, it assigned next number after using the same number for repeated numbers. for DEPTNO 30 and SAL 1250 ranking value 2 was used. But after that value 3 starts means it didn’t skip 3. This is the main difference between rank and dense_rank in SQL.
Now in the final step, we will understand how row_number in SQL works and the difference between rank dense_rank and row_number in SQL. Syntax for row_number in SQL is given as below.
row_number() OVER([ query_partition_clause ] order_by_claus
It is very clear from the above SQL syntax that syntax is almost the same. Now let’s understand how to write SQL queries for row_number in oracle.
SELECT empno, deptno, sal, row_number() OVER (PARTITION BY deptno ORDER BY sal) AS myrownumber FROM emp_name;
Here we can see that it just provides a sequence number and no number is skipped or repeated.
Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() function in SQL
The difference boils down to the fact when ties happen. In the case of the tie,
- ROW_NUMBER() will give unique row numbers,
- the rank will give the same rank, but the next different rank will not be in sequence, there will be a gap.
- In the case of dense_rank, both rows in the tie will have the same rank and there will be no gap. The next different rank will be in sequence.
You can read more on SQL ranking function from this link.