SQL SERVER – Steps to Identify with Odd and Even Rows

Sometimes, when working with SQL, you might need to do something special with odd and even rows in your table. This can be a bit tricky. This blog post will look at two simple ways to do this.

Approach 1: Using a Temporary Table

The first method uses a temporary table. Here’s how you can do it:

1. Start by making a new table. This table will have a column called “id” that counts up from 1 for each row and a column for your data (we’ll call it “order_num”).
2. Next, put the data from your original table into the new table.
3. Now, you can easily find the odd rows by checking if the id number is odd.

CREATE TABLE #tbl_temp (
    id INT IDENTITY(1, 1) PRIMARY KEY,
    order_num INT
);
INSERT INTO #tbl_temp (order_num)
SELECT order_num
FROM original_table;
-- Odd Rows
SELECT order_num
FROM #tbl_temp
WHERE id % 2 = 1;
-- Even Rows
SELECT order_num
FROM #tbl_temp
WHERE id % 2 = 0;

Approach 2: Using a CTE and ROW_NUMBER() Function

The second method uses a common table expression (CTE) and the ROW_NUMBER() function.

1. Create a CTE that gives each row in your original table a row number.
2. Then, use the CTE to find the odd rows.

;WITH cte AS
(
    SELECT order_num, ROW_NUMBER() OVER (ORDER BY order_num) AS row_num
    FROM original_table
)SELECT order_num
FROM cte
WHERE row_num % 2 = 1;

Conclusion – Odd and Even

This blog post taught us two easy ways to work with odd and even rows in a T-SQL table. These methods can help when you need to do something special with these rows. Remember, the best method depends on what you’re trying to do.

If you are using any other method, do post over here. I would like to learn from you, and maybe we can create a follow-up blog post with due credit to you.

You can always reach out to me on Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)