MySQL Query to Select Top 10 Records?

Each row in the Customers table represents a unique customer and contains various details about them. One of these details is the customer’s Order_Val, which represents the total value of all orders that the customer has placed.

Your task is to write an SQL query that selects information about the top 10 customers who have the highest order values. The customers should be sorted in descending order of their Order_val, meaning the customer with the highest Order Value should be first.

Retrieving the Top 10 Records in MySQL

Query to create Customer Table

CREATE TABLE Customers (
    SNo INT,
    name VARCHAR(255),
    Order_val INT
);

Insert data in the Customers table:

INSERT INTO Customers (SNo, name, Order_val) VALUES 
(1, 'Clark', 129),
(2, 'Dave', 220),
(3, 'Ava', 3629),
(4, 'ABC', 21),
(5, 'Shubham', 6999),
(6, 'Piyush', 29),
(7, 'XYZ', 1999),
(8, 'Manu', 2320),
(9, 'Ram', 1629),
(10, 'DEF', 199),
(11, 'GHI', 2320);

Output:

Table

Example of Retrieving the Top 10 Records in MySQL

Example 1: Using Subquery

A Subquery in SQL is a query that is embedded within another query. It can be used in various parts of a query, including the SELECT, FROM, WHERE, and HAVING clauses. The result of a subquery can be used as an input to the main query.

It involves creating a subquery that retrieves the top 10 records in ascending order (from worst to best) and then ordering these records in descending order in the outer query. This method might be useful in scenarios where you need to perform additional operations on the top records, such as joining them with another table.

Query:

  SELECT * FROM ( SELECT * FROM Customers
    ORDER BY Order_val DESC
    LIMIT 10
) sub
ORDER BY Order_val DESC;

Explanation:

1. SELECT * FROM Customers ORDER BY Order_val DESC LIMIT 10: This is a subquery that selects all columns (*) from the Customers table, sorts the records in descending order (DESC) by the Order_val column, and limits the result to the top 10 records (LIMIT 10)
2. SELECT * FROM (…) sub ORDER BY Order_val DESC: This is the outer query. It selects all columns from the result of the subquery (aliased as sub) and then sorts these records again in descending order by the Order_val column.

Output:

Using subquery

Explanation:

This query retrieves the top 10 records from the Customers table based on the highest Order_val values and then reorders them in descending order. The final output displays the selected records sorted by the Order_val column in descending order.

Example 2: Using ORDER BY and LIMIT

The LIMIT clause is used to set an upper limit on the number of tuples returned by SQL.

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.

This is the most straightforward and efficient method. The ORDER BY clause sorts the records based on the specified column, and the DESC keyword specifies that the sorting should be in descending order (from best to worst). The LIMIT 10 clause then limits the result to the top 10 records. This method is best when you simply want to retrieve the top records.

Query:

SELECT * FROM Customers
ORDER BY Order_val DESC
LIMIT 10;

Explanation:

1. SELECT * FROM Customers: This part of the query selects all columns (*) from the Customers table.
2. ORDER BY Order_val DESC: This clause sorts the records in descending order (DESC) by the Order_val column. This means that records with higher values in the Order_val column will appear first.
3. LIMIT 10: This clause limits the result to the top 10 records. Since the records are sorted in descending order by Order_val, these will be the 10 records with the highest values in the Order_val column.

Output:

Using ORDER BY and LIMIT

Explanation: This query selects the top 10 records from the Customers table, ordering them in descending order based on the Order_val column. The output consists of the highest Order_val records from the table.

Conclusion

The output of these queries will be a table with all the columns from the Customers table, but only for the top 10 customers with the highest OrderValue. The rows will be sorted so that the customer with the highest OrderValue is first, the customer with the second highest OrderValue is second, and so on, until the customer with the tenth highest OrderValue.

In SQL, The results of a SELECT statement are sorted using the ORDER BY clause. The number of tuples can be limited by LIMIT clause returned by SQL.