SQL | Top-N Queries

Top-N queries retrieve a specified number of rows (N) from the top of a result set, typically based on a sorting criterion. These types of queries are common in situations where you want to see, for example, the top 10 selling products, the 5 least performing employees, etc.

Different databases have various ways of achieving top-N results:

1. SQL Server:

In SQL Server, you can use the TOP keyword:

SELECT TOP 10 product_name, sales_amount
FROM products
ORDER BY sales_amount DESC;

Combine with WITH TIES if you want to retrieve additional rows that tie with the last row:

SELECT TOP 10 product_name, sales_amount
FROM products
ORDER BY sales_amount DESC
WITH TIES;

2. Oracle:

Oracle has the ROWNUM and, in more recent versions, the FETCH FIRST clause:

Using ROWNUM:

SELECT product_name, sales_amount
FROM (
    SELECT product_name, sales_amount
    FROM products
    ORDER BY sales_amount DESC
)
WHERE ROWNUM <= 10;

Using FETCH FIRST (from Oracle 12c onward):

SELECT product_name, sales_amount
FROM products
ORDER BY sales_amount DESC
FETCH FIRST 10 ROWS ONLY;

3. MySQL:

MySQL uses the LIMIT clause:

SELECT product_name, sales_amount
FROM products
ORDER BY sales_amount DESC
LIMIT 10;

4. PostgreSQL:

PostgreSQL also uses the LIMIT clause:

SELECT product_name, sales_amount
FROM products
ORDER BY sales_amount DESC
LIMIT 10;

In PostgreSQL 13 and later, you can also use FETCH FIRST:

SELECT product_name, sales_amount
FROM products
ORDER BY sales_amount DESC
FETCH FIRST 10 ROWS ONLY;

5. SQLite:

SQLite uses the LIMIT clause:

SELECT product_name, sales_amount
FROM products
ORDER BY sales_amount DESC
LIMIT 10;

Notes:

Always combine your top-N queries with an ORDER BY clause to ensure you're retrieving the top N rows based on specific criteria.
Remember that the word "top" can be misleading; it's about the criteria, not the order of data in the database. So, a "top 10" query with an ascending order on a date column would give you the earliest 10 dates.
For scenarios where you need to paginate results (like in web applications), you can use OFFSET in conjunction with LIMIT in databases like MySQL, PostgreSQL, and SQLite. This can be used to skip the first set of results and show the next, aiding in pagination.

In all scenarios, ensure you have appropriate indexing to support the sorting for optimal performance.

How to use TOP in SQL:
- The TOP keyword is used in SQL Server to limit the number of rows returned.
```
SELECT TOP 5 column1, column2
FROM example_table;
```
Retrieving the top N rows in SQL:
- Use TOP to retrieve the top N rows from a table.
```
SELECT TOP 10 column1, column2
FROM example_table;
```
Using LIMIT and OFFSET for Top-N queries:
- For databases like MySQL and PostgreSQL, use LIMIT and OFFSET for Top-N queries.
```
SELECT column1, column2
FROM example_table
ORDER BY column1
LIMIT 5 OFFSET 0;
```
Top-N queries with ORDER BY in SQL:
- Combine TOP with ORDER BY for sorting and retrieving the top N rows.
```
SELECT TOP 5 column1, column2
FROM example_table
ORDER BY column1 DESC;
```

SQL FETCH FIRST vs. TOP for Top-N queries:

Use FETCH FIRST in databases like Oracle and PostgreSQL instead of TOP.

-- Oracle and PostgreSQL
SELECT column1, column2
FROM example_table
ORDER BY column1 DESC
FETCH FIRST 5 ROWS ONLY;

Handling ties in Top-N queries in SQL:

Handle ties using WITH TIES in SQL Server or window functions.

-- SQL Server
SELECT TOP 5 WITH TIES column1, column2
FROM example_table
ORDER BY column1 DESC;

-- PostgreSQL
SELECT column1, column2
FROM example_table
ORDER BY column1 DESC
LIMIT 5;

Getting random rows with TOP in SQL:
- Combine TOP with ORDER BY NEWID() to get random rows.
```
SELECT TOP 5 column1, column2
FROM example_table
ORDER BY NEWID();
```

Dynamic values for Top-N queries in SQL:

Use variables or parameters for dynamic Top-N queries.

DECLARE @n INT = 5;
SELECT TOP (@n) column1, column2
FROM example_table
ORDER BY column1 DESC;

Top-N queries with GROUP BY in SQL:
- Use TOP with GROUP BY to get the top N rows for each group.
```
SELECT TOP 5 WITH TIES column1, column2
FROM example_table
ORDER BY column1 DESC;
```

Pagination using TOP in SQL:

Implement pagination with TOP, ORDER BY, and dynamic values.

DECLARE @page INT = 2;
DECLARE @pageSize INT = 5;
SELECT column1, column2
FROM example_table
ORDER BY column1
OFFSET (@page - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;

Alternatives to TOP for Top-N queries:

Use LIMIT and OFFSET for MySQL and PostgreSQL, and FETCH FIRST for Oracle and PostgreSQL.

-- MySQL
SELECT column1, column2
FROM example_table
ORDER BY column1 DESC
LIMIT 5;

-- Oracle and PostgreSQL
SELECT column1, column2
FROM example_table
ORDER BY column1 DESC
FETCH FIRST 5 ROWS ONLY;