I am working with SQL Server 2008. I have a table which does not contain any unique columns; how to get alternate rows from it?
SQL Server table:
+-----+--------+
| id | name |
|-----+--------|
| 1 | abc |
| 2 | pqr |
| 2 | pqr |
| 3 | xyz |
| 4 | lmn |
| 5 | efg |
| 5 | efg |
+-----+--------+
As we've to come with at least one working suggestion with the question, I've tried below code; which is not so proper technique when fetching from a huge amount of data.
Trial:
create table #tmp
(
id int, name varchar(10), srNo int
)
insert into #tmp
select
id, name,
ROW_NUMBER() OVER (ORDER BY id) % 2 as srNo --,alternate rows
from
Employee
select *
from #tmp
where srNo = 1 --or srNo = 0
Above query gives out alternate rows i.e. 1st, 3rd, 5th OR 2nd, 4th, 6th etc.
Please help me out with proper way without #tmp to achieve the goal!
Thank you!
You can just use your select statement as an in-line view. You don't need the #tmp 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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments