Looping through a recordset in the select statement

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Looping through a recordset in the select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-23 : 08:54:38

Virendra writes "Dear Sir,
I have a Table A which does not have any primary key. It has a field Price. I want to write a select statement that returns price*2 for every alternate records. For example if i have 10 records, for the 2,4,6,8 and 10th record, the price returned should be price*2. Is it possible in one single SELECT statement??
Hoping to get a quick reply.

Thanks and Regards,
VSRajput"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-23 : 09:55:05

OK, first of all, if you don't have a primary key, you don't have a table. [CELKO]. Said differently, without a unique way to identify a tuple, you are not in 1NF.

Second, in relational theory, there is no concept of record order (barring an ORDER BY clause, of course). So the 2,4,6,8 and 10th record is a meaningless concept.

Having said that....


create table #virendra (price int)
insert #virendra
select 1
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
go

select
    price,
    price * 2 as [pricetimestwo]
from
    #virendra v
where
    ((select count(*)
     from #virendra
     where v.price > price) % 2) = 1
order by
    price

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources