You are here:Home » tsql » Various ways to get TOP records from table - SQL Server

Various ways to get TOP records from table - SQL Server

We confused sometimes during write the code logic for SQL queries when we required some of top records from tables to implement in logic.

This is very basic thing for the development and here i am going to demonstrate some of the options to get top records using TOP clause  from table in various ways.

-- Let's create table

IF ( Object_id('TopRecords') > 0 )
  DROP TABLE TopRecords

GO

CREATE TABLE TopRecords
  (
     Id      INT IDENTITY(1, 1),
     Records VARCHAR(50)
  )

GO

-- Inserting records in table

DECLARE @Cnt INT

SET @Cnt = 1

WHILE ( @Cnt <= 100 )
  BEGIN
      INSERT INTO TopRecords
                  (Records)
      SELECT CONVERT(VARCHAR, @Cnt) + '_xyz'

      SET @Cnt = @Cnt + 1
  END

GO


#1 way

-- Selecting top 1 records by declaring and assiging value to varible

DECLARE @top INT

SET @top = 10 -- set @top to 10 to get top 10 record from restult

SELECT TOP (@top) Id,
                  Records
FROM   TopRecords

#2 way

-- Seleting top 10 records from table by set rows count in query

DECLARE @top INT

SET @top = 10 -- set @top to 10 to get top 10 record from restult
SET ROWCOUNT @top --set rowcount to 10

SELECT Id,
       Records
FROM   TopRecords

SET ROWCOUNT 0 --- Reset rowcount back to 0


#3 way

-- Seleting top 10 records using dynamic query

DECLARE @SQL VARCHAR(500)
DECLARE @top INT

SET @top = 10
SET @SQL = '
SELECT TOP ' + CONVERT(VARCHAR, @top) + ' Id, Records FROM TopRecords
'

EXEC (@SQL)

GO


#4 way



;WITH TopRecords_CTE AS (
SELECT id,records,ROW_NUMBER() OVER(ORDER BY id) row FROM TopRecords
)
SELECT id,records FROM TopRecords_CTE
WHERE row BETWEEN 1 AND 10

this is usually used when paging is needed for a large datasets.


Please share your knowledge if have any other ways to get the top records from table.