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:
- 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”).
- Next, put the data from your original table into the new table.
- 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.
- Create a CTE that gives each row in your original table a row number.
- 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)
1 Comment. Leave new
# 3rd way, if table have any unique number/date column.
select * from original_table a
where ((select count(*) from original_table b where b.num < a.num)+1) %2 = 1 –Odd
select * from original_table a
where ((select count(*) from original_table b where b.num < a.num)+1) %2 = 0 –Even