Select Even Number of Records From Table In SQL Server 2008

In this article, I will explain how to split a table in two equal half's to select even number of records from table.

Create Table

CREATE TABLE [dbo].[Student1Details](

      [ID] [int] NOT NULL,

      [Name] [varchar](50) NULL,

      [Branch] [varchar](10) NULL,

      [Location] [varchar](10) NULL,

 CONSTRAINT [PK_Student1] PRIMARY KEY CLUSTERED

(

      [ID] ASC

)

) ON [PRIMARY]

Insert some values

INSERT INTO Student1Details

SELECT 1, 'Nitin', 'CS','IND' UNION ALL

SELECT 2, 'Ravi', 'EI','ENG' UNION ALL

SELECT 3, 'Tim', 'ME','US' UNION ALL

SELECT 4, 'Rick', 'ME','IND' UNION ALL

SELECT 5, 'Rakesh', 'CS','ABD' UNION ALL

SELECT 6, 'Tarun', 'ME','IND' UNION ALL

SELECT 7,'Raushan','IT','IND' UNION ALL

SELECT 8,'Aman','EC','US'

SELECT * FROM Student1Details

Student1Details Table

To show only the odd rows, write following query in which we ROW_NUMBER() which returns the sequential  number of row in a given recordset, where you defines on how to number the records. Here we use RowNumber % 2 = 0 to return even number of records.

Example

SELECT * FROM (

    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber FROM [Student1Details]

) A

WHERE RowNumber % 2 = 0

Output: