Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sweshni
Starting Member
2 Posts |
 Posted - 2009-09-09 : 13:48:30
|
| I just wanted to take only the alternative records from a table without using where clause. for eg. out of 5 rows in a table i want to retrieve only 1st,3rd and 5th record. Can anyone give me the answer please? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-09-09 : 15:23:14
|
Without using where clause?Nice joke 
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
shzkhan28
Starting Member
15 Posts |
Posted - 2009-09-09 : 22:51:53
|
quote:
Originally posted by sweshni
I just wanted to take only the alternative records from a table without using where clause. for eg. out of 5 rows in a table i want to retrieve only 1st,3rd and 5th record. Can anyone give me the answer please?
I think below code will help u.Create table tbl1 (fld1 char(2),fld2 char(20))Insert into tbl1 VALUES ('A1','LINE ONE')Insert into tbl1 VALUES ('A2','LINE TWO')Insert into tbl1 VALUES ('A3','LINE THREE')Insert into tbl1 VALUES ('A4','LINE FOUR')Insert into tbl1 VALUES ('A5','LINE FIVE')SELECT ROW_NUMBER() OVER (ORDER BY FLD1) As rowid,* into #tmp1 FROM tbl1 select * from #tmp1 where (rowid%2) <> 0 |
|
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-09-09 : 23:59:13
|
quote:
Originally posted by shzkhan28
quote:
Originally posted by sweshni
I just wanted to take only the alternative records from a table without using where clause. for eg. out of 5 rows in a table i want to retrieve only 1st,3rd and 5th record. Can anyone give me the answer please?
I think below code will help u.Create table tbl1 (fld1 char(2),fld2 char(20))Insert into tbl1 VALUES ('A1','LINE ONE')Insert into tbl1 VALUES ('A2','LINE TWO')Insert into tbl1 VALUES ('A3','LINE THREE')Insert into tbl1 VALUES ('A4','LINE FOUR')Insert into tbl1 VALUES ('A5','LINE FIVE')SELECT ROW_NUMBER() OVER (ORDER BY FLD1) As rowid,* into #tmp1 FROM tbl1 select * from #tmp1 where (rowid%2) <> 0
Hi,I think Row_number() is not work in sql2000 version |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-09-10 : 00:25:40
|
| try like thisCreate table #tbl1 (fld1 char(2),fld2 char(20))Insert into #tbl1 VALUES ('A1','LINE ONE')Insert into #tbl1 VALUES ('A2','LINE TWO')Insert into #tbl1 VALUES ('A3','LINE THREE')Insert into #tbl1 VALUES ('A4','LINE FOUR')Insert into #tbl1 VALUES ('A5','LINE FIVE')SELECT identity(int,1,1) As rowid,* into #tmp1 FROM #tbl1select * from #tmp1 where (rowid%2) <> 0drop table #tbl1,#tmp1 |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-10 : 02:42:14
|
quote:
Originally posted by sweshni
I just wanted to take only the alternative records from a table without using where clause. for eg. out of 5 rows in a table i want to retrieve only 1st,3rd and 5th record. Can anyone give me the answer please?
Why do you want to do this?MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
select alternative records from a table