SQL ORDER BY LIMIT – Retrieve Specific Rows from a Database

SQL ORDER BY LIMIT

When working with large datasets, it’s often necessary to fetch a limited number of records. This is particularly useful in pagination, where you might want to display only a fixed number of results per page—such as 10, 50, or 100.

In SQL, the ORDER BY clause helps us arrange records in a specific order, while the LIMIT clause (or its equivalent in different databases) helps us restrict the number of rows returned.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Using LIMIT in SQL (MySQL, PostgreSQL, etc.)

In databases like MySQL and PostgreSQL, you can use the LIMIT clause directly to fetch a specific number of rows.

Example: Fetch the top 20 youngest employees

SELECT name, age 
FROM employee_data 
ORDER BY age ASC 
LIMIT 20;

This will return the youngest 20 employees based on age.

LIMIT Equivalent in Oracle SQL – Using ROWNUM

Oracle does not support LIMIT directly. Instead, we use ROWNUM to filter rows after sorting the results.

Example: Fetching records from 21st to 40th position

SELECT employee_name, employee_salary  
FROM   
(SELECT employee_name, employee_salary, ROWNUM r  
FROM   
(SELECT employee_name, employee_salary  
FROM company_employees  
ORDER BY employee_salary DESC  
)  
WHERE ROWNUM <= 40  
)  
WHERE r >= 21;  

Explanation:

• The inner query first sorts all employees by salary in descending order.
• The second query assigns a row number (ROWNUM) to each row and filters it to first 40 records.
• The outer query extracts records from 21st to 40th.

Why Use ORDER BY with LIMIT?

1. Efficient Pagination:
   • Fetch only necessary records instead of loading the entire dataset.
   • Improves performance in applications with large databases.
2. Sorting and Filtering:
   • Helps retrieve top N records based on specific criteria (e.g., highest salary, latest transactions).
3. Better User Experience:
   • Used in websites, dashboards, and mobile apps to load data faster in chunks.

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Final Thoughts

The ORDER BY LIMIT approach is a powerful way to control how data is retrieved, sorted, and displayed. While MySQL and PostgreSQL support LIMIT directly, Oracle requires ROWNUM-based queries to achieve the same effect. By using these techniques, you can optimize your SQL queries for better performance and scalability.

For more SQL tips, tutorials, and advanced database concepts, stay tuned to UpdateGadh! 🚀

sql order by limit offset
order by limit 1 vs max
mysql order by limit
order by desc limit 1 sql server
sql limit
limit with order by in oracle
order by and limit in postgres
desc limit sql
sql order by
sql order by offset
sql order by limit example
sql order by limit multiple columns
sql order by limit oracle