SQL Select Top N or Top N Random Rows, SQL TOP
You may need a sql query that will select top n records or random n records for each category in a table. The t-sql query that will solve this problem may be difficult for first timers, especially if you are working on MS SQL Server 2000. Now, with the t-sql enhancements in Microsoft SQL Server 2005 the problem of selecting a definite number of records grouped or categorized according to a column is easier to create.
Select Top 1 row or select only first record
If you need to select top 1 row or select only the first record from a SQL Server database table, you can use the below t-sql syntax.
SELECT TOP 1 * FROM dbo.Customers
We can want to select first record from customes ordered by alphabetically, in this case for a solution we will use an ORDER BY clause.
SELECT TOP 1 * FROM dbo.Customers ORDER BY [Customer Name] ASC
SELECT TOP 1 * FROM dbo.Customers ORDER BY [Customer Name] DESC
Select Top 10 rows or select first 10 records
If you need to select top 10 rows or select first 10 rows of a sql database table, you can use the below t-sql syntax.
SELECT TOP 10 * FROM dbo.Customers
Select Top N rows or select first N records
If you need to select top n rows or select first n rows from a database table, you can use the below t-sql syntax.
Here the important point is the N rows is dynamically set so we need to declare a variable for the first N rows and then select the first group among others.
DECLARE @N int
SET @N = 3
SELECT TOP (@N) * FROM dbo.Customers
If you do not place @N among paranthesis, the SQL Server engine will throw the following error.
Incorrect syntax near '@N'.
SELECT TOP Random n Rows From a Table For Each Category or Group
Refer to SELECT TOP Random n Rows From a Table For Each Category or Group
Random Sorting using NEWID()
Refer to Random Ordering using NEWID()