SQL Select Top
The SELECT TOP statement in SQL shows the limited number of records or rows from the database table. The TOP clause in the statement specifies how many rows are returned. It shows the top N number of rows from the tables in the output. This clause is used when there are thousands of records stored in the database tables.
Let’s take a simple example: If a Student table has a large amount of data about students, the select TOP statement determines how much student data will be retrieved from the given table.
Example:
SELECT TOP number | percent column_Name1, column_Name2, ….., column_NameN FROM table_name WHERE [Condition] ;
In this syntax, the pattern is a sequence of characters that have to be searched in that column which is just specified after the WHERE clause.
Let’s take the following Employee table which helps you to analyze the LIKE operator with % sign:
Suppose, you want to show the first three Names and Color of Car from the above table. To do this, you have to type the following query in SQL:
SELECT TOP 3 Car_Name, Car_Color FROM Cars;
This query shows the following table:
We have a table called Student with three columns:
Suppose, you want to show the details of the first four students in the result from the above table. To do this, you have to type the following query in SQL:
SELECT TOP 4 * FROM Student;
This query shows the following table:
We have a table called Employee with four columns:
Suppose, you want to show the details of those first four employees whose city is Goa from the above table. To do this, you have to type the following query in SQL:
SELECT TOP 4 * FROM Employee WHERE Emp_City = Goa ;
This query shows the following table:
Course Video
NOTE: Practice below practice questions on MSSQL SERVER, it will not execute on site editor.
1.Write a SQL query to select top 5 rows from Person.Address table.
(person.address)-(5 row count)
2. Write SQL Query to get top 7 rows and get columns BusinessID, Name, AccountNumber, ActiveFlag from Purchasing.vendor table.
(Purchasing.vendor)-(row count 7)
3. Write a SQL query to get 2 percent rows from person table.
(person.person)-(400 row count)
4. Make a SQL Query to find top 11 rows and only want Name and ModifiedDate from Sales.Currency table
(Sales.Currency) (11 rows count)
5. Write a SQL Query to get all the columns from HumanResources.Employee table with top 9 values.
(HumanResources.Employee)-(9 row count)
6. Write an SQL query to retrieve the top 10 PurchaseOrderDetailID values where the OrderQty is equal to 550.
7. Write an SQL query to retrieve the top 50 BusinessEntityID, PersonType, FirstName, and LastName where the PersonType is ‘IN’.
8. Write an SQL query to retrieve the top 5 records from the ‘ScrapReason’ table.
9. Write an SQL query to retrieve the top 5 records from the ‘ProductSubCategory’ table where the ProductCategoryID is equal to 2.
10. Write an SQL query to retrieve the top 20 records from the ‘EmployeePayHistory’ table where the Rate is greater than 30.
YouTube Reference :
1) SQL Select Top in Hindi/Urdu
2) SQL Select Top in English
The SQL SELECT TOP statement is used to limit the number of rows returned in a query result. It’s often applied to retrieve the first few rows from a table.
The basic syntax is:
sql
Copy code
SELECT TOP (number) column_name(s)
FROM table_name
WHERE condition;
For example, SELECT TOP 5 * FROM Employees; retrieves the first 5 rows from the Employees table.
Advanced examples include:
- Combining SELECT TOP with ORDER BY to sort data before limiting rows.
- Using it with aggregate functions like COUNT or SUM.
• SELECT TOP is specific to SQL Server.
• LIMIT is used in MySQL for the same purpose. For example: sql Copy code SELECT TOP 10 * FROM table_name; // SQL Server SELECT * FROM table_name LIMIT 10; // MySQL
sql
Copy code
SELECT TOP 1 * FROM table_name ORDER BY column_name DESC;
This fetches the single record with the highest or lowest value, depending on the sort order.
Yes, our page offers a free and detailed SQL SELECT TOP Tutorial, complete with examples and use cases.
Yes, you can combine SELECT TOP with the WHERE clause to filter results. For example: sql Copy code SELECT TOP 3 * FROM Employees WHERE department = ‘Sales’;
- Using it in databases that don’t support the syntax.
- Forgetting to pair it with ORDER BY when the row order is important.
Yes, SELECT TOP can be used in queries with JOINs to limit results from joined tables. For example: sql Copy code SELECT TOP 5 a.column1, b.column2 FROM table1 a JOIN table2 b ON a.id = b.id;
No, it’s primarily supported in SQL Server. Alternatives like LIMIT (MySQL) or FETCH FIRST (Oracle) are used in other database systems.
