SQL Tip #3 – Odd and Even records

If you ever need to display odd and even records from the MS SQL Server database, following sql queries could be a help.

Sample Table:

EmployeeTable

Odd Rows

SQL 1:

SELECT Main.* FROM Employee Main
CROSS APPLY
(
    SELECT COUNT(EmployeeId) AS Ctr
    FROM Employee
    WHERE EmployeeId <= Main.EmployeeId
) AS Sub
WHERE Ctr % 2 <> 0
ORDER BY Main.EmployeeId

SQL 2:

SELECT EmployeeId,
EmployeeName,
Salary
FROM
(
    SELECT ROW_NUMBER()
    OVER (ORDER BY EmployeeId) AS RowId,*
    FROM Employee
) AS Wrap
WHERE RowId % 2 <> 0

 

Even Rows

SQL 1:

SELECT Main.* FROM Employee Main
CROSS APPLY
(
     SELECT COUNT(EmployeeId) AS Ctr
     FROM Employee
     WHERE EmployeeId <= Main.EmployeeId
) AS Sub
WHERE Ctr % 2 = 0
ORDER BY Main.EmployeeId

SQL 2:

SELECT EmployeeId,
 EmployeeName,
 Salary
FROM
(
     SELECT ROW_NUMBER()
     OVER (ORDER BY EmployeeId) AS RowId,*
     FROM Employee
) AS Wrap
WHERE RowId % 2 = 0

 

In both the cases SQL 2 performs well.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s