Skip to the content
Start

Numbering and ranking

SQL · Unit 13: Window functions

HR is drawing up the salary list, from highest to lowest. What number does each person get?

ROW_NUMBER() numbers the rows. It doesn't take a column, so its parentheses stay empty. The order it numbers in comes from an ORDER BY written inside OVER, which decides the numbers and doesn't sort the result. To sort it, you still need the usual ORDER BY, at the end. Here the rows usually come out already sorted, because of how the engine works, and nothing promises it.

Samuel and Lydia both earn 3000. The id breaks the tie and decides that Samuel goes first. Without it, SQL doesn't promise who gets the 4 and who gets the 5.

SELECT name, salary,
  ROW_NUMBER() OVER (
    ORDER BY salary DESC, id
  ) AS num
FROM employees
ORDER BY num;

Returns

namesalarynum
Carol52001
Paige41002
Mark38003
Samuel30004
Lydia30005
Frank26006
Grace24007
Nicole21008
Rachel19009

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    The Sales list, department 1, this time numbered from the lowest salary to the highest. Mark earns 3800, Nicole 2100, and Rachel 1900. What does this query return?

  2. 2. Put the lines in order

    A single list for Sales and Tech, departments 1 and 2. Put in order the query that numbers their seven people from the highest salary to the lowest, with the id to break ties, and shows them in alphabetical order. Two lines don't belong.

  3. 3. Predict the output

    Tech, ranked from the lowest salary to the highest. Frank earns 2600, Samuel and Lydia 3000 each, and Paige 4100. What does this query return?

  4. 4. Predict the output

    The company is going to raise the salary of anyone earning one of the three lowest salaries among the people who earn 2400 to 3800. If two people earn the same, both are in. Grace earns 2400, Frank 2600, Samuel and Lydia 3000 each, and Mark 3800. What does this query return?

  5. 5. Find the bug

    The Tech ranking, from the highest salary to the lowest. Samuel and Lydia both earn 3000, and they have to share second place. The query runs, and Lydia is third. Which line has the problem?

  6. 6. Complete the code

    Each person's place within their department, from the highest salary to the lowest, for Sales and Tech, departments 1 and 2. Fill in the window's two keywords.

  7. 7. Put the lines in order

    HR is numbering the people in Sales and Design, departments 1 and 3. Put in order the query that gives each person their number within their department, from the highest salary to the lowest, and shows them in alphabetical order. Two lines don't belong.

  8. 8. Write the code

    The two rankings together. Write a query with three columns, in this order: the name, each person's place in the whole company, and their place within their department, both from the highest salary to the lowest. People who earn the same share a place, and after a tie a number is skipped. All nine have to be on the list, sorted by their place in the company and then by name.

  9. 9. Find the case that fails

    The welcome card goes with one order per customer, the first. This is the query from unit 9, which today returns six orders, one for each customer who has bought something. Which of these changes makes it send two cards to the same person?

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →