In the data managing systems, MariaDB stands as a robust and adaptable MariaDB stands as a robust and adaptable choice in data managing systems. It’s known for efficiently retrieving data with lots of tools and instructions. We’re looking at the SELECT TOP clause, a key part of fetching data from MariaDB tables. The SELECT TOP in MariaDB limits the number of rows as queried results. we can MariaDB stands as a robust and adaptable choice in the data managing systems for the top N rows, by a set order criteria.
How to Select Top Rows
In the MariaDB to select TOP N rows and TOP 10 rows firstly we have to use the LIMIT clause with the SELECT query, and how the select query, Order By Clause works. We will understand through various examples.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC]
LIMIT N;
Explanation:
- SELECT column1, column2: Specify the columns we want to retrieve from the table or * to retrieve everything.
- FROM table_name: This shows the name of the table from which we want to fetch the data.
- ORDER BY column_name [ASC | DESC]: The ORDER BY column_name [ASC | DESC] clause is optional. This clause helps to sort results, ascending order is by default (ASC) but we can specify DESC for descending order.
- LIMIT N: This clause is important for selecting the top rows. It limits the number of rows returned in the result set to the specified value N.
Examples of How to Select the Top 10 Rows From Table
For better understanding we will create an table called salaries to perform various queries and operations.
Query:
CREATE TABLE salaries
(
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2),
hired DATE
);
Output:
salaries table
Example 1: Let’s Fetch Only Top 10 Records From the Salaries
Suppose we want to fetch all columns from the “salaries” table and limits the result set to the first 10 rows.
Query:
SELECT * FROM salaries LIMIT 10;Output:
only 10 records
Explanation: In this query, We have limited the output to the top 10 records only.
Example 2: Let’s Fetch TOP 10 Highest(maximum) Salaries
Suppose we want to fetch all columns from the “salaries” table, orders the result set in descending order based on the “salary” column, and then limits the output to the first 10 rows.
Query:
SELECT * FROM salaries ORDER by salary DESC LIMIT 10;Output:
max 10 salaries
Explanation: In this query, We are fetching top 10 salary records by sorting(ordering descending) by salary.
Example 3: Let’s Fetch TOP 10 Salary Records for Employees Who Hired in the Year 2023
Suppose we want to fetch the “name” and “salary” columns from the “salaries” table for employees hired in the year 2023 and limits the output to the first 10 rows.
Query:
SELECT name, salary FROM salaries
WHERE YEAR(hired) = 2023 LIMIT 10;
Output:
year 2023 salary
Explanation: In this query, we are retrieving the name and salary of TOP 10 who hired in 2023.
Example 3: Suppose We to need to Retrieve the Name and Salary of the Top 10 Employees Who were Hired Before ‘2023-01-01’ and have a salary More Than the Average Salary.
Suppose we want to fetch the “name” and “salary” columns from the “salaries” table for employees hired before January 1, 2023, with a salary greater than the average salary of all employees. The result set is ordered by salary in descending order, and the output is limited to the first 10 rows.
Query:
SELECT name, salary
FROM salaries
WHERE hired < '2023-01-01' AND
salary > (SELECT AVG(salary) FROM salaries)
ORDER BY salary DESC
LIMIT 10;
Output:
top 10 have more than average salary
Explanation: In this query we have fetched top 10 records which were hired before ‘2023-01-01‘ and having salary more than the average salary.
Example 4: Let’s Retrieve the Name, Salary, and the Difference Between the Salary
Suppose we want to fetch the “name,” “salary,” and calculates the “salary_difference” by subtracting the average salary of all employees in the “salaries” table. The below query focuses on employees hired in the year 2022, orders the result set by the salary difference in descending order, and limits the output to the first 10 rows.
Query:
SELECT name, salary,
(salary - AVG(salary) OVER ()) AS salary_difference
FROM salaries
WHERE YEAR(hired) = 2022
ORDER BY salary_difference DESC
LIMIT 10;
Output:
output
Explanation: In the above Query, We are fetching the name and salary of 2022 hired in this query. we are also showing how their salaries compare to the average. It’s ranked from highest to lowest difference.we are limiting it to the top 10.
Conclusion
In the MariaDb slecting top N rows from a table is easy using with the LIMIT clause. This clause helps manage in the big datasets. It sets a limit on the number of rows a query returns. This helps us to improve speed and the use of resources. It could be taking the top N records using some conditions or clauses. The LIMIT clause in MariaDB gives flexibility and control in handling data.