The LIMIT (in MySQL, PostgreSQL, SQLite) or TOP (in SQL Server) keyword in SQL is used to restrict the number of rows returned by a query. It’s particularly useful when you want to preview a dataset, implement pagination, or fetch only the top-performing records based on certain criteria.
For example, in MySQL or PostgreSQL, we use:
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;
This query retrieves the top 5 employees with the highest salaries.
In SQL Server, the equivalent would be:
SELECT TOP 5 * FROM employees
ORDER BY salary DESC;
Both achieve the same goal — returning a limited set of rows instead of the entire dataset.
In a real-world scenario, I used LIMIT while building an analytics dashboard that displayed the Top 10 selling products. The underlying SQL query used LIMIT along with ORDER BY to ensure only the top results were fetched, which improved performance and load time.
One challenge I faced was implementing pagination when dealing with millions of rows. Using LIMIT with OFFSET like:
SELECT * FROM sales
ORDER BY sale_date DESC
LIMIT 10 OFFSET 1000;
can become slower for higher offsets, because the database still scans all the skipped rows internally. To optimize that, I later used keyset pagination (using WHERE clauses with indexed columns) which was much faster for large data.
A limitation of LIMIT or TOP is that they only control how many rows are returned — they don’t guarantee consistent results unless used with ORDER BY, because without it, the order of records is not fixed.
As an alternative, some databases like Oracle use FETCH FIRST n ROWS ONLY or ROWNUM for the same purpose.
So overall, LIMIT or TOP is extremely handy for improving query efficiency, especially when dealing with large datasets or when only a subset of data is required — like previews, dashboards, or top-N analysis.