Select Odd 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 odd number of records from table.
  • 4546

In this article, I will explain how to split a table in two equal half's to select odd 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

StudentTable.jpg

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 = 1 to return odd number of records.

Example

SELECT * FROM (

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

) A

WHERE RowNumber % 2 = 1

Output:

OddRecords.jpg

© 2013 dotNetheaven. All rights reserved.