Rank in SQL

How to use row_number in SQL | SQL RANK() function | dense_rank SQL

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.

how to use row_number in SQL
how to use row_number in SQL

INSERT in SQL

As we have created Oracle table now the time has come to INSERT INTO table in oracle. We will use the below SQL query to insert in SQL table. You can read more on SQL INSERT from this link.

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;

SQL SELECT

After INSERT SQL statement check data by using SQL SELECT statement.

INSERT INTO statement
INSERT INTO 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.

what does rank mean
what does rank mean

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.

SQL dense_rank

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;
sql ranking
SQL ranking

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.

SQL row_number

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;
row_number in oracle
row_number in oracle

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,

  1. ROW_NUMBER() will give unique row numbers,
  2. the rank will give the same rank, but the next different rank will not be in sequence, there will be a gap.
  3. 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.

Leave a Comment