How to Select Odd or Even Rows in SQL Server?

In this tutorial, I am going to show you how to select Odd Or Even Rows in SQL Server. Here is the steps for the same.

Create Table:


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](150) NULL,
[Gender] [varchar](20) NULL,
[DOB] [date] NULL,
[Salary] [float] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Insert Records:

INSERT INTO [dbo].[Employee]([Name],[Gender],[DOB],[Salary]) VALUES('Vinod Palne','Male' ,'08/10/1990' ,30000)
INSERT INTO [dbo].[Employee]([Name],[Gender],[DOB],[Salary]) VALUES('Dheeraj Gupta','Male' ,'08/10/1986' ,20000)
INSERT INTO [dbo].[Employee]([Name],[Gender],[DOB],[Salary]) VALUES('Vinay Kumar','Male' ,'08/10/1989' ,25000)
INSERT INTO [dbo].[Employee]([Name],[Gender],[DOB],[Salary]) VALUES('Seema Palne','Female' ,'08/10/1987' ,20000)
INSERT INTO [dbo].[Employee]([Name],[Gender],[DOB],[Salary]) VALUES('Suraj Kumar','Male' ,'08/10/1989' ,27000)

Query For Selecting Even Rows:

Method 1:

SELECT [Name],[Gender],[DOB],[Salary] FROM (SELECT ROW_NUMBER() Over(ORDER BY ID) AS Row,* FROM [dbo].[Employee]) A WHERE Row%2=0

Output:

Odd Or Even Rows

Method 2:

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Row, * FROM [dbo].[Employee]
)
SELECT [ID] , [Name]
,[Gender]
,[DOB]
,[Salary] FROM CTE WHERE Row%2=0

Output:

Odd Or Even Rows

You can see the output of both queries is the same.

Query For Selecting Odd Rows:

Method 1:

   SELECT [Name],[Gender],[DOB],[Salary] FROM (SELECT ROW_NUMBER() Over(ORDER BY ID) AS Row,* FROM [dbo].[Employee]) A WHERE Row%2=1

Output:

Odd Or Even Rows

Method 2:

 WITH CTE AS
 (
 SELECT ROW_NUMBER() OVER (ORDER BY ID) AS Row, * FROM [dbo].[Employee]
 )
 SELECT [ID] , [Name]
 ,[Gender]
 ,[DOB]
 ,[Salary] FROM CTE WHERE Row%2=1

Output:

Odd Or Even Rows

 

Conclusion:

I hope this is a helpful post for you. You can also comment for the technical question. I will also love to have your feedback and suggestions.

Thank You.

 

Leave a Comment