Row-by-row-operations - 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)

Row-by-row-operations

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-11-10 : 05:13:25

Hi

I have the follow table:


TimeField        | Fld1 
2010-10-10 00:00 | 120
2010-10-10 01:00 | 200
2010-10-10 01:21 | 314

And i want to get follow result:

TimeField        | Fld1 | Fld2
2010-10-10 00:00 | 120  |
2010-10-10 01:00 | 200  | 120
2010-10-10 01:21 | 314  | 200
2010-10-10 02:01 | 412  | 314

and so on

How could i get that ?

note:
The goal is simulate an row-by-row-operation so i can take the different between to rows. But still don't know how achieve below yet.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 05:42:36

[code]declare @Sample table (TimeField datetime, Fld1 int)
insert @Sample
select '2010-10-10T00:00:00.000', 120 union all
select '2010-10-10T01:00:00.000', 200 union all
select '2010-10-10T02:01:00.000', 314

select * from @Sample

select s1.TimeField,s1.Fld1,s2.Fld1 as Fld2
from
(
select
row_number() over (order by TimeField) as rownum,
*
from @Sample
) as s1
left join
(
select
row_number() over (order by TimeField) as rownum,
*
from @Sample
) as s2
on s1.rownum = s2.rownum + 1
[/code]

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

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-11-10 : 05:59:29

quote:

Originally posted by webfred

declare @Sample table (TimeField datetime, Fld1 int)
insert @Sample
select '2010-10-10T00:00:00.000', 120 union all
select '2010-10-10T01:00:00.000', 200 union all
select '2010-10-10T02:01:00.000', 314

select * from @Sample

select s1.TimeField,s1.Fld1,s2.Fld1 as Fld2
from
(
 select
 row_number() over (order by TimeField) as rownum,
 *
 from @Sample
) as s1
left join
(
 select
 row_number() over (order by TimeField) as rownum,
 *
 from @Sample
) as s2
on s1.rownum = s2.rownum + 1

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

Thanks Webfred

This works for these few lines,
But if i have millions of data, how could i generalize this query?

or if we focus on the first part of your solutions,
How could we achieve that for millions of records? without produce
it manually ?

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 06:07:49

The @Sample table and the inserts are ONLY to have some sample data...

The solution begins here:

select s1.TimeField,s1.Fld1,s2.Fld1 as Fld2
from
(
 select
 row_number() over (order by TimeField) as rownum,
 *
 from @Sample
) as s1
left join
(
 select
 row_number() over (order by TimeField) as rownum,
 *
 from @Sample
) as s2
on s1.rownum = s2.rownum + 1

Just replace the table name and the column names with your real object names.

I can't say if this solution is efficient for million of rows but I even don't know another solution in this moment.

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

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-11-10 : 06:21:33

quote:
Originally posted by webfred
I can't say if this solution is efficient for million of rows but I even don't know another solution in this moment.

Thanks Webfred!
That really solve the case.
Yes it took about 2 seconds for my millions of data.
I consider that as a good result

Thanks again!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-10 : 07:05:04

2 seconds? That's better than I hoped for.

Welcome

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

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-11-10 : 08:36:37

quote:
Originally posted by webfred

2 seconds? That's better than I hoped for.

Welcome

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

yeah, nasa's computer have never fail me before.
Well now i have an little break to take, and gonna play quake3

;-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources