Select alternate rows from SQL Server table

select t.id, name
from (select id, name, ROW_NUMBER() over (order by id) as srNo from Employee) t
where (t.srNo % 2) = 1

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,* FROMSTUDENTS)

A WHERE ROW%2=0







Method 2:

WITH CTE AS

(

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

)

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



 



Method 3:

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

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



 



Method 4:

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

 SELECT STUDENT_ID,STUDENT_NAME,DOB,DEPARTMENT_ID,DOJ FROMTEMP_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.