Module 14advanced

Window Functions

Take Quiz

Lessons

Open Console Module Quiz

ROW_NUMBER

1 / 2

ROW_NUMBER assigns a unique number to each row. Use PARTITION BY to restart numbering within groups.

Sample Tables Used in This Course

employeesEmployee records

Column	Type	Key
id	INTEGER	PK
name	TEXT
department	TEXT
salary	INTEGER
country	TEXT
manager_id	INTEGER	FK → employees.id
email	TEXT
hire_date	TEXT

Sample Data (first 5 rows)

id	name	department	salary	country	manager_id	email	hire_date
1	Ahmed Al-Thani	IT	25000	Qatar	NULL	ahmed@qatarco.qa	2018-03-15
2	Fatima Al-Kuwari	HR	18000	Qatar	1	fatima@qatarco.qa	2019-06-01
3	Mohammed Al-Sulaiti	Finance	22000	Qatar	1	mohammed@qatarco.qa	2017-09-20
4	Sara Al-Mansouri	IT	20000	Qatar	1	sara@qatarco.qa	2020-01-10
5	Khalid Hassan	IT	17000	Egypt	4	khalid@qatarco.qa	2021-04-05

ordersOrder transactions

Column	Type	Key
id	INTEGER	PK
customer_id	INTEGER	FK → customers.id
amount	DECIMAL
order_date	TEXT
region	TEXT
status	TEXT

Sample Data (first 5 rows)

id	customer_id	amount	order_date	region	status
1	1	15000	2024-01-15	Doha	completed
2	2	8500	2024-01-20	Doha	completed
3	3	22000	2024-02-01	Doha	pending
4	1	5000	2024-02-10	Al Wakrah	completed
5	4	12000	2024-02-15	Lusail	shipped

Relationships: Primary Keys (PK) uniquely identify rows. Foreign Keys (FK) reference other tables.

Practice with these tables in the SQL Console. Click "Open Console" in the sidebar.

Example

text-blue-600 dark:text-blue-400 font-semibold">SELECT name, department, salary,
  text-blue-600 dark:text-blue-400 font-semibold">ROW_NUMBER() text-blue-600 dark:text-blue-400 font-semibold">OVER (text-blue-600 dark:text-blue-400 font-semibold">PARTITION BY department text-blue-600 dark:text-blue-400 font-semibold">ORDER BY salary text-blue-600 dark:text-blue-400 font-semibold">DESC) text-blue-600 dark:text-blue-400 font-semibold">AS text-blue-600 dark:text-blue-400 font-semibold">rank
text-blue-600 dark:text-blue-400 font-semibold">FROM employees;

Ranks employees within each department by salary

Learning Objectives

Use ROW_NUMBER for ranking
Compare rows with LAG/LEAD
Understand PARTITION BY

Previous Module