loops,ROW_NUMBER,cursor - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

loops,ROW_NUMBER,cursor

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-03-17 : 05:16:39

i have a query like this:


SELECT StartDate,R1,SUM(Total),ROW_NUMBER() OVER(ORDER BY StartDate ) AS 'id'
  FROM [MonthlySummary] (NOLOCK)
  Group by [StartDate],R1
  Order by StartDate,R1

is there a way to loop through these result, withought using cursor, and based on the id value i get from the ROW_NUMBER?

Thanks
Peleg

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 05:26:17

[code]
SELECT what_you_want ...
FROM
(
SELECT StartDate,R1,SUM(Total) as [Total],ROW_NUMBER() OVER(ORDER BY StartDate ) AS [id]
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1
) as DT
WHERE [id] = what_you_want
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-17 : 05:30:42

Try like this

declare @index int
declare @count int
SELECT @count=count(StartDate)
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1
set @index=1
while(@index<=@count)
begin
select * from (SELECT StartDate,R1,SUM(Total),ROW_NUMBER() OVER(ORDER BY StartDate ) AS id
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1)a
where id=@index
set @index=@index+1
end

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 05:33:24

quote:
Originally posted by senthil_nagore

Try like this

declare @index int
declare @count int
SELECT @count=count(StartDate)
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1
set @index=1
while(@index<=@count)
begin
select * from (SELECT StartDate,R1,SUM(Total) as [Total],ROW_NUMBER() OVER(ORDER BY StartDate ) AS id
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1)a
where id=@index
set @index=@index+1
end

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-17 : 05:41:26

Thanks webfred!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-03-17 : 05:43:55

both of the answer's makes it requery my first query all the time,isn't it in this case put it once in a Temp Table, and then go over it wors?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-17 : 05:47:24

quote:
Originally posted by pelegk2

both of the answer's makes it requery my first query all the time,isn't it in this case put it once in a Temp Table, and then go over it wors?

Temp table will be a good solution, if the table size is small use table variable instead of temp table

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-17 : 05:47:45

quote:
Originally posted by pelegk2

both of the answer's makes it requery my first query all the time,isn't it in this case put it once in a Temp Table, and then go over it wors?

See,this is what u look for.

declare @a table

(
cnt int,
fname varchar(50)
)

insert @a

select 1,'a' union all
select 4,'a' union all
select 2,'b' union all
select 6,'b'

;with cte as
(
select * from
(
select sum(cnt)as cnt,fname,ROW_NUMBER()over(order by fname)as id from @a group by fname
)as t
)

select ct.*,t.* from cte as ct cross apply (select top 1 * from cte where id=ct.id)t

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-17 : 05:52:26

quote:
Originally posted by pelegk2

both of the answer's makes it requery my first query all the time,isn't it in this case put it once in a Temp Table, and then go over it wors?

I believe the optimizer can handle this in a good way.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 06:16:20

quote:
Originally posted by pelegk2

i have a query like this:

SELECT StartDate,R1,SUM(Total),ROW_NUMBER() OVER(ORDER BY StartDate ) AS 'id'
FROM [MonthlySummary] (NOLOCK)
Group by [StartDate],R1
Order by StartDate,R1

is there a way to loop through these result, withought using cursor, and based on the id value i get from the ROW_NUMBER?

Thanks
Peleg

Can you tell us what you are trying to do?

Madhivanan

Failing to plan is Planning to fail

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-03-17 : 09:36:14

i have a table like this
id,StartDate, [name],value
1, 20100101000000,'x1' ,33
2, 20100101000000,'x2' ,32
3, 20100101000000,'yx' ,11
4, 20100201000000,'yx' ,88
5, 20100201000000,'x2' ,15

and i want to make from this table
another table like this
[name],20100101000000,20100201000000
'x1' ,33 ,0
'x2' ,32 ,15
'yx' ,11 ,88
i want this structure, so i will be able to go over the rows, and just write then line by line to a file

Thanks
Peleg

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-18 : 03:22:51

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources