Finding TOP values from the table using SELECT TOP command

SQL

When working with databases, the need to retrieve a specific number of rows is a common scenario. This is where the “SELECT TOP” clause in SQL comes into play. It allows you to limit the number of rows returned in a query result, making your data retrieval more precise and efficient.

Basic Syntax

The basic syntax of the “SELECT TOP” clause is simple:

SELECT TOP (number) column1, column2, ...
FROM table_name;

SELECT TOP (number): Specifies the number of rows to be retrieved.
column1, column2, …: Lists the columns you want to include in the result.
FROM table_name: Specifies the table from which you want to retrieve data

Practical Example

Consider a hypothetical scenario where you have a “students” table with columns “student_id,” “student_name,” and “marks.”

Database Table: students

student_id	student_name	marks
1	Alice	85
2	Bob	92
3	Charlie	78
4	David	95
5	Emily	88

Example 1: Retrieve Top 3 Students based on Marks

SELECT TOP 3 student_id, student_name, marks
FROM students
ORDER BY marks DESC;

This query retrieves the top 3 students with the highest marks, ordering the result set in descending order based on the “marks” column.

student_id	student_name	Marks
4	David	95
2	Bob	92
5	Emily	88

Example 2: Retrieve Students with Marks above 80

SELECT student_id, student_name, marks
FROM students
WHERE marks > 80;

student_id	student_name	marks
2	Bob	92
4	David	95
5	Emily	88

Here are a few additional points to consider:

OFFSET-FETCH Clause (SQL Server):

In SQL Server, you can use the OFFSET and FETCH clauses in conjunction with the ORDER BY clause to implement pagination. This allows you to skip a certain number of rows and then return a specific number of rows from the result set.

Example:

SELECT column1, column2, ...
FROM table_name
ORDER BY some_column
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

This query skips the first 10 rows and then returns the next 5 rows from the result set.

Ties Handling (SQL Server):

In SQL Server, the WITH TIES option with the TOP clause can be used to include additional rows with the same values as the last row in the result set.

Example:

SELECT TOP 5 WITH TIES column1, column2, ...
FROM table_name
ORDER BY some_column;

If multiple rows share the same values as the last row, they will be included in the result set.