2.4 Sorting and Filtering: ORDER BY, LIMIT, and DISTINCT

Sorting and filtering help refine query results in SQL. The ORDER BY, LIMIT, and DISTINCT clauses provide control over how data is displayed, making it easier to analyze and focus on specific aspects of the dataset.

2.4.1 ORDER BY Clause

The ORDER BY clause sorts query results based on one or more columns, either in ascending (ASC) or descending (DESC) order.

• Syntax:

  SELECT column1, column2, ...
  FROM table_name
  ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
  

• Key Points:
  • By default, sorting is in ascending (ASC) order.
  • Multiple columns can be used for sorting, with priority from left to right.
• Example 1: Sort by Single Column
  Retrieve all employees sorted by their salary in ascending order:

  SELECT name, salary
  FROM employees
  ORDER BY salary;
  

• Example 2: Sort by Multiple Columns
  Retrieve employees sorted by department (ascending), and within each department, by salary (descending):

  SELECT name, department, salary
  FROM employees
  ORDER BY department ASC, salary DESC;
  

2.4.2 LIMIT Clause

The LIMIT clause restricts the number of rows returned by a query. This is useful when you need to analyze or display only a subset of data.

• Syntax:

  SELECT column1, column2, ...
  FROM table_name
  LIMIT number_of_rows;
  

• Key Points:
  • The LIMIT value must be a non-negative integer.
  • It is often used in conjunction with ORDER BY to retrieve top or bottom records.
• Example 1: Retrieve Top 5 Employees by Salary

  SELECT name, salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 5;
  

• Example 2: Paginate Results (with OFFSET)
  Retrieve the next 5 rows starting from the 6th row:

  SELECT name, salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 5 OFFSET 5;
  

2.4.3 DISTINCT Clause

The DISTINCT clause eliminates duplicate rows in query results, ensuring each row is unique based on the specified columns.

• Syntax:

  SELECT DISTINCT column1, column2, ...
  FROM table_name;
  

• Key Points:
  • Use DISTINCT when duplicate data exists and unique records are needed.
  • Works across single or multiple columns.
• Example 1: Retrieve Unique Departments

  SELECT DISTINCT department
  FROM employees;
  

• Example 2: Retrieve Unique Combinations
  Retrieve unique combinations of department and job titles:

  SELECT DISTINCT department, job_title
  FROM employees;
  

Combining ORDER BY, LIMIT, and DISTINCT

You can combine these clauses for more refined queries:

1. Find the Top 3 Unique Departments with the Highest Average Salaries:

   SELECT DISTINCT department, AVG(salary) AS avg_salary
   FROM employees
   GROUP BY department
   ORDER BY avg_salary DESC
   LIMIT 3;
   

2. Retrieve the First 10 Unique Products Alphabetically:

   SELECT DISTINCT product_name
   FROM products
   ORDER BY product_name
   LIMIT 10;
   

Real-World Applications

1. E-Commerce:
   • Display the top 10 most expensive products.

     SELECT product_name, price
     FROM products
     ORDER BY price DESC
     LIMIT 10;
     

2. Employee Management:
   • Find departments where unique job titles exist.

     SELECT DISTINCT department, job_title
     FROM employees;
     

3. Sales Analysis:
   • Retrieve the top 5 sales transactions by amount.

     SELECT transaction_id, amount
     FROM sales
     ORDER BY amount DESC
     LIMIT 5;
     

Conclusion

The ORDER BY, LIMIT, and DISTINCT clauses are essential tools for organizing and filtering query results. They enable precise control over how data is displayed, making it easier to analyze, report, and make data-driven decisions. Mastering these clauses enhances your ability to work efficiently with SQL databases.