SQL Sorting Records

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

sorting

ORDER BY

• Query the products table and sort them by price (Min → Max)

SELECT *
FROM products
ORDER BY price;

• Query the products table and sort them descending (Max → Min)

SELECT *
FROM products
ORDER BY price DESC;

• Or force it to be ascending (Min → Max)

SELECT *
FROM products
ORDER BY price ASC;

• If there are 2 variablels

SELECT *
FROM products
ORDER BY price, weight DESC;

Offset and Limit

• Skip the first three rows of the result set → OFFSET 3
• Only give the first two rows of the result set → LIMIT 2

-- start with counting all rows
SELECT COUNT(*) from users;

-- skip first 40 rows
SELECT *
FROM users
OFFSET 40;

-- get only 5 rows
SELECT *
FROM users
LIMIT 5;

-- sort them and limit first 5 rows
SELECT *
FROM products
ORDER BY price
LIMIT 5;

• Write a query that show the names of only the second and third most expensive phones

SELECT name
FROM phones
ORDER BY price DESC
LIMIT 2
OFFSET 1;