SQL ORDER BY LIMIT

In SQL, the ORDER BY clause is used to sort the result set returned by a query based on one or more columns. It allows you to arrange the rows in either ascending or descending order according to the specified column(s). This clause is essential for organizing data in a meaningful way, especially when dealing with large datasets.

The ORDER BY clause is often combined with the LIMIT clause to restrict the number of rows returned by a query. The LIMIT clause specifies the maximum number of rows to return, which is particularly useful when you only need to retrieve a subset of the sorted data. By using LIMIT, you can improve query performance and reduce unnecessary data transfer.

Together, the ORDER BY and LIMIT clauses provide powerful capabilities for sorting and limiting the results of SQL queries, enabling efficient data retrieval and analysis.

We can retrieve limited rows from the database. I can be used in pagination where are forced to show only limited records like 10, 50, 100 etc.

Syntax

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

Examples of ORDER BY LIMIT

Example 1: Sorting and Limiting Results

-- Create a sample table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO employees (employee_id, name, salary) VALUES
(1, 'Alice', 50000.00),
(2, 'Bob', 60000.00),
(3, 'Charlie', 75000.00),
(4, 'David', 65000.00),
(5, 'Eve', 70000.00);

-- Query to retrieve top three highest-paid employees
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Output

  name    |  salary  
----------+----------
 Charlie  | 75000.00
 Eve      | 70000.00
 David    | 65000.00

Example 2: Sorting and Limiting by Alphabetical Order

-- Create a sample table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- Insert sample data
INSERT INTO products (product_id, name) VALUES
(1, 'Chair'),
(2, 'Desk'),
(3, 'Table'),
(4, 'Bookcase'),
(5, 'Lamp');

-- Query to retrieve first three products alphabetically
SELECT name
FROM products
ORDER BY name
LIMIT 3;

Output

   name   
----------
 Bookcase
 Chair
 Desk

Example 3: Sorting and Limiting by Date

-- Create a sample table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO orders (order_id, order_date, total_amount) VALUES
(1, '2024-04-25', 150.00),
(2, '2024-04-26', 200.00),
(3, '2024-04-27', 180.00),
(4, '2024-04-24', 220.00),
(5, '2024-04-23', 175.00);

-- Query to retrieve details of the three most recent orders
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 3;

Output

 order_id | order_date | total_amount 
----------+------------+--------------
        3 | 2024-04-27 |       180.00
        2 | 2024-04-26 |       200.00
        1 | 2024-04-25 |       150.00

Example 4: Sorting and Limiting by Aggregate Function

-- Create a sample table
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    quantity_sold INT
);

-- Insert sample data
INSERT INTO sales (sale_id, product_id, quantity_sold) VALUES
(1, 1, 50),
(2, 2, 30),
(3, 1, 40),
(4, 3, 60),
(5, 2, 20);

-- Query to retrieve top three products with the highest total quantity sold
SELECT product_id, SUM(quantity_sold) AS total_quantity_sold
FROM sales
GROUP BY product_id
ORDER BY total_quantity_sold DESC
LIMIT 3;

Output

 product_id | total_quantity_sold 
------------+---------------------
          1 |                  90
          3 |                  60
          2 |                  50

Conclusion

The combination of ORDER BY and LIMIT in SQL queries provides a powerful mechanism for sorting data and retrieving a subset of rows based on specified criteria. Through these clauses, database developers and analysts can efficiently manage and manipulate query results to meet their requirements.

In this exploration, we’ve observed how ORDER BY enables sorting of query results in ascending or descending order, while LIMIT restricts the number of rows returned by the query. By using these clauses together, we can fine-tune our queries to retrieve the top or bottom results based on certain attributes such as numerical values, alphabetical order, or date/time stamps.

The examples provided demonstrate the versatility of ORDER BY with LIMIT, showcasing scenarios such as retrieving top-paid employees, selecting the first products alphabetically, obtaining the most recent orders, and identifying top-selling products. These functionalities are invaluable in various domains, including business intelligence, e-commerce, data analysis, and reporting.

It’s important to note that while ORDER BY with LIMIT offers significant flexibility and efficiency, it’s essential to consider the performance implications, especially when dealing with large datasets. Optimizing queries, indexing relevant columns, and understanding the underlying database system’s execution plan can help mitigate any potential performance bottlenecks.

In conclusion, the combination of ORDER BY and LIMIT enriches the SQL querying experience, empowering users to tailor their queries precisely to their needs. By leveraging these clauses effectively, practitioners can extract valuable insights from their data and enhance the functionality and efficiency of their SQL-based applications.

Frequently Asked Questions