DbSchema PostgreSQL starts when you begin the lesson

Lesson 5

Sorting and limiting: ORDER BY and LIMIT

For readers who finished lesson 4 and can filter rows with WHERE.

After this lesson you can sort an answer, take its first rows, and break ties, so that a top three is always the same three.
  • Rows come in no set order
  • ORDER BY
  • Descending, and a second column
  • LIMIT
  • Ties
  • 4 exercises

Start the lesson

Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.

For a reference page on the same statements, read SQL ORDER BY: Syntax, ASC/DESC, Multiple Columns, and Pagination.

The idea

Without ORDER BY, rows come in no set order

Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date
Hover a table or a column to read its comment.
select distinct country
from customers;

The countries come in no visible order. PostgreSQL returns rows in whatever order is quickest to produce, and nothing promises that this order stays the same.

The idea

ORDER BY sorts the answer

Products 1 to 6 as stored, with prices 24.90, 89.00, 39.50, 45.00, 59.90 and 279.00; sorted by price, the same rows run from 24.90 to 279.00.

ORDER BY comes after WHERE and names the column to sort by. It sorts from small to large, A to Z and early to late, unless you say otherwise.

Try it

Sort the products by price

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
select name, price
from products
order by price;

Cheapest first: Cable Organizer at 12.50. Ascending is the default.

Try it

DESC sorts from large to small

Write desc after the column to turn the order round.

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
select name, price
from products
order by price desc;

Most expensive first: Workstation Tower at 1899.00.

Try it

A second column sorts the rows that tie

Several orders share a date. A second column after a comma decides their order, and only theirs.

Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
select order_id, order_date
from orders
order by order_date desc, order_id desc;

Orders 1737, 1097 and 457 share 2026-10-02; the higher id comes first.

Try it

LIMIT keeps the first rows of the answer

LIMIT comes last and keeps that many rows. After an ORDER BY, it gives the top of the list.

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
select name, price
from products
order by price
limit 5;

The five cheapest products, from 12.50 to 42.00.

Watch out

Ties make a top three unpredictable

🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) manager_id Table public.employees ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who work for the shop.employees 🔑 Pk employees_pkey ( employee_id ) ⧉ employee_id * int4 ↙ employees( manager_id ) ↙ orders( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the employee.employee_id 🔗 Referred by employees ( manager_id -> employee_id ) Referred by orders ( employee_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the employee.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the employee.last_name 🔍 Unq employees_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The work email address. No two employees share one.email ⧉ department_id * int4 ↗ departments( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The department the employee works in: a department_id of departments.department_id 🔗 References departments ( department_id ) ⧉ manager_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id 🔗 References employees ( manager_id -> employee_id ) ⧉ hire_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the employee started.hire_date ⧉ salary * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The yearly salary, with two decimals.salary
Hover a table or a column to read its comment.

Six employees, the department heads, share the top salary of 95,000. Asked for the three best paid, PostgreSQL picks three of them:

select first_name, last_name, salary
from employees
order by salary desc
limit 3;
first_namelast_namesalary
OlgaCosta95000.00
BenNovak95000.00
HugoLopez95000.00

Nothing says which three, or in which order.

Try it

Break the tie with a second sort column

Sort the employees with the same salary by employee_id, which is different for every row, and the answer is the same on every run.

🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) manager_id Table public.employees ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who work for the shop.employees 🔑 Pk employees_pkey ( employee_id ) ⧉ employee_id * int4 ↙ employees( manager_id ) ↙ orders( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the employee.employee_id 🔗 Referred by employees ( manager_id -> employee_id ) Referred by orders ( employee_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the employee.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the employee.last_name 🔍 Unq employees_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The work email address. No two employees share one.email ⧉ department_id * int4 ↗ departments( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The department the employee works in: a department_id of departments.department_id 🔗 References departments ( department_id ) ⧉ manager_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id 🔗 References employees ( manager_id -> employee_id ) ⧉ hire_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the employee started.hire_date ⧉ salary * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The yearly salary, with two decimals.salary
Hover a table or a column to read its comment.
select first_name, last_name, salary
from employees
order by salary desc, employee_id
limit 3;

Hugo, Olga and Ben, employees 1 to 3, in that order, every time.

Exercise 1 of 4

Row order counts

Show every product's name and price, the most expensive first.

Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock
Hover a table or a column to read its comment.
Expected result

It shows once PostgreSQL is ready.


    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select name, price from products order by price desc

    






    
Exercise 2 of 4
Row order counts

    




Show the name and price of the five cheapest products, the cheapest first.










  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  










Table public.products
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      What the shop sells.products
  🔑  Pk products_pkey ( product_id ) ⧉  product_id
     * int4
 ↙ order_items( product_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the product.product_id
🔗  Referred by order_items ( product_id ) 
  🔍  Unq products_name_key ( name ) ⧉  name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The name of the product. No two products share a name.name
  ⧉  category
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The group the product belongs to, such as Accessories or Displays.category
  ⧉  price
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The current price, with two decimals. Always more than 0.price
  ⧉  stock
     * int4
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      How many pieces are in the warehouse. Never below 0.stock


Hover a table or a column to read its comment.





    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select name, price from products order by price limit 5

    






    
Exercise 3 of 4
Row order counts

    




Show the id and date of the ten most recent orders, the newest first. For orders on the same date, the higher id comes first.










  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  










Table public.orders
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      One purchase by one customer. Its products are the rows of order_items.orders
  🔑  Pk orders_pkey ( order_id ) ⧉  order_id
     * int4
 ↙ order_items( order_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the order.order_id
🔗  Referred by order_items ( order_id ) 
  ⧉  customer_id
     * int4
 ↗ customers( customer_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The customer who placed the order: a customer_id of customers.customer_id
🔗  References customers ( customer_id ) 
  ⧉  employee_id
     int4
 ↗ employees( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id
🔗  References employees ( employee_id ) 
  ⧉  order_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the order was placed.order_date
  ⧉  status
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      Where the order stands: pending, shipped, delivered or cancelled.status


Hover a table or a column to read its comment.




    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select order_id, order_date from orders order by order_date desc, order_id desc limit 10

    






    
Exercise 4 of 4
Row order counts

    




Show the first name, last name and salary of the three best-paid employees. For equal salaries, the lower employee_id comes first.












  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  







🔗  Foreign Key employees_manager_id_fkey
employees ➔ employees ( manager_id -> employee_id )

🔗  Foreign Key employees_manager_id_fkey
employees ➔ employees ( manager_id -> employee_id )

manager_id




Table public.employees
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The people who work for the shop.employees
  🔑  Pk employees_pkey ( employee_id ) ⧉  employee_id
     * int4
 ↙ employees( manager_id )
 ↙ orders( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the employee.employee_id
🔗  Referred by employees ( manager_id -> employee_id ) 
Referred by orders ( employee_id ) 
  ⧉  first_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The first name of the employee.first_name
  ⧉  last_name
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The last name of the employee.last_name
  🔍  Unq employees_email_key ( email ) ⧉  email
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The work email address. No two employees share one.email
  ⧉  department_id
     * int4
 ↗ departments( department_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The department the employee works in: a department_id of departments.department_id
🔗  References departments ( department_id ) 
  ⧉  manager_id
     int4
 ↗ employees( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id
🔗  References employees ( manager_id -> employee_id ) 
  ⧉  hire_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the employee started.hire_date
  ⧉  salary
     * numeric(10,2)
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The yearly salary, with two decimals.salary


Hover a table or a column to read its comment.




    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
select first_name, last_name, salary from employees order by salary desc, employee_id limit 3