How To Select Alternate Rows From A Table In SQL Server

How to select alternate rows from a table in SQL Server

Alternate rows from a table:

CREATE TABLE STUDENTS(

      STUDENT_ID int NULL,

      [STUDENT_NAME] [varchar](50) NULL,

      DOB date NULL,

      DEPARTMENT_ID int NULL,

      DOJ date NULL

      )

GO   

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (1,N'BALACHANDAR', '1983-10-28', 2, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (2,N'PREMKUMAR', '1986-06-17', 1, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (3,N'MADHUSOODHAN', '1988-06-30', 3, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (4,N'SAGARBABU', '1995-10-05', 4, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (5,N'SRAVANTHI', '1988-04-06', 5, GETDATE())

The following data is present in the table .

 

Method 1:

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM(

SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS)

A WHERE ROW%2=0

Method 2:

WITH CTE AS

(

SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS

)

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM CTE WHERE ROW%2=0

 

Method 3:

SELECT  ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* INTO _TEMP_STUDENTS FROMSTUDENTS

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM _TEMP_STUDENTS WHEREROW%2=0

 

Method 4:

 SELECT IDENT=IDENTITY(int, 1,1),* into TEMP_STUDENTS  FROM STUDENTS

 SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM TEMP_STUDENTS        WHERE IDENT%2=0

 

All the results are same and returning the alternate rows from the table 'STUDENTS'


To learn more about selecting alternate rows from the SQL. Please check out the links on this page.

 

How to select alternate rows from a table in SQL Server

Alternate rows from a table:

CREATE TABLE STUDENTS(

      STUDENT_ID int NULL,

      [STUDENT_NAME] [varchar](50) NULL,

      DOB date NULL,

      DEPARTMENT_ID int NULL,

      DOJ date NULL

      )

GO   

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (1,N'BALACHANDAR', '1983-10-28', 2, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (2,N'PREMKUMAR', '1986-06-17', 1, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (3,N'MADHUSOODHAN', '1988-06-30', 3, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (4,N'SAGARBABU', '1995-10-05', 4, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (5,N'SRAVANTHI', '1988-04-06', 5, GETDATE())

The following data is present in the table .

 

Method 1:

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM(

SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS)

A WHERE ROW%2=0

Method 2:

WITH CTE AS

(

SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS

)

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM CTE WHERE ROW%2=0

 

Method 3:

SELECT  ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* INTO _TEMP_STUDENTS FROMSTUDENTS

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM _TEMP_STUDENTS WHEREROW%2=0

 

Method 4:

 SELECT IDENT=IDENTITY(int, 1,1),* into TEMP_STUDENTS  FROM STUDENTS

 SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM TEMP_STUDENTS        WHERE IDENT%2=0

 

All the results are same and returning the alternate rows from the table 'STUDENTS'


To learn more about selecting alternate rows from the SQL. Please check out the links on this page.

How to select alternate rows from a table in SQL Server

Alternate rows from a table:

CREATE TABLE STUDENTS(

      STUDENT_ID int NULL,

      [STUDENT_NAME] [varchar](50) NULL,

      DOB date NULL,

      DEPARTMENT_ID int NULL,

      DOJ date NULL

      )

GO   

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (1,N'BALACHANDAR', '1983-10-28', 2, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (2,N'PREMKUMAR', '1986-06-17', 1, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (3,N'MADHUSOODHAN', '1988-06-30', 3, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (4,N'SAGARBABU', '1995-10-05', 4, GETDATE())

INSERT STUDENTS (STUDENT_ID, STUDENT_NAME, DOB, DEPARTMENT_ID, DOJ) VALUES (5,N'SRAVANTHI', '1988-04-06', 5, GETDATE())

The following data is present in the table .

 

Method 1:

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM(

SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS)

A WHERE ROW%2=0

Method 2:

WITH CTE AS

(

SELECT ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* FROM STUDENTS

)

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM CTE WHERE ROW%2=0

 

Method 3:

SELECT  ROW_NUMBER()OVER (ORDER BY STUDENT_ID)AS ROW,* INTO _TEMP_STUDENTS FROMSTUDENTS

SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM _TEMP_STUDENTS WHEREROW%2=0

 

Method 4:

 SELECT IDENT=IDENTITY(int, 1,1),* into TEMP_STUDENTS  FROM STUDENTS

 SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROM TEMP_STUDENTS        WHERE IDENT%2=0

 

All the results are same and returning the alternate rows from the table 'STUDENTS'