Ravindra BagaleCourses & study guides

10. MySQL Part 1: Install, Create, Insert and SELECT

10.6 ORDER BY, LIMIT, DISTINCT and Aliases

By Last updated: 

SELECT name, fees_paid FROM students ORDER BY fees_paid DESC;       -- highest first
SELECT name, city, age FROM students ORDER BY city ASC, age DESC;   -- two-level sort
SELECT name, fees_paid FROM students ORDER BY fees_paid DESC LIMIT 3;   -- top 3
SELECT name FROM students ORDER BY student_id LIMIT 3 OFFSET 3;     -- rows 4-6 (pagination)
SELECT DISTINCT city FROM students;                                 -- unique values
SELECT name AS student_name, fees_paid * 1.18 AS fees_with_tax FROM students;
SELECT s.name, s.city FROM students AS s WHERE s.age < 23;          -- table alias

Why this matters for security

Attackers use ORDER BY 1, ORDER BY 2, … to find out how many columns a vulnerable query returns (the query breaks when the number is too big). This is the first step of a UNION-based SQL injection – you will see it in the DVWA lab.

Ravindra Bagale's Tip

If you run SELECT * on a large table without LIMIT, the screen fills with lakhs of rows – and on production the server slows down. Many students make this mistake. When exploring, always add LIMIT 10.

Practice task

List the three youngest students, list unique cities alphabetically, and show page 2 of students when each page has 4 rows.