Value Chnages - Need Help - 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

General SQL Server Forums

New to SQL Server Programming

Value Chnages - Need Help

Author

Topic

Deepak1983
Starting Member

23 Posts

Posted - 2010-12-10 : 13:34:23

Mean Date
3.8 7/1/2010 0:00
3.8 7/2/2010 0:00
2.1 7/3/2010 0:00
2.1 7/5/2010 0:00
3.8 7/6/2010 0:00
4.1 7/7/2010 0:00
4.1 7/8/2010 0:00
2.1 7/9/2010 0:00

This is my table and i want result below :-
Mean Date
3.8 7/1/2010 0:00
2.1 7/3/2010 0:00
3.8 7/6/2010 0:00
4.1 7/7/2010 0:00
2.1 7/9/2010 0:00

This means i only want rows where the value gets change for first time.

Deeps

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 13:40:57

select mean, min(date) as date from table group by mean

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

Deepak1983
Starting Member

23 Posts

Posted - 2010-12-10 : 13:42:58

Well it will not give accurate result, it will not show the same value second time, for example 2.1 of 7/9/2010 again in the query you provided..

quote:
Originally posted by webfred

select mean, min(date) as date from table group by mean

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

Deeps

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 13:46:58

That's not possible unless your data has changed between first and second execution.

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 13:49:12

Ah! Now I understand...

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

Deepak1983
Starting Member

23 Posts

Posted - 2010-12-10 : 13:50:06

I just want the value changes for first time. Immaterial of what the value is?

quote:
Originally posted by webfred

That's not possible unless your data has changed between first and second execution.

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

Deeps

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-10 : 14:15:56

;with cte_Mean
AS(
SELECT Mean,Date,[Row] = row_number() OVER( order by date)
FROM @table
)
select t1.*
from cte_mean t1
left join cte_mean t2 on t1.row = t2.row + 1 and t1.mean = t2.mean
where t2.mean is null

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 14:16:53

select
dt1.mean, dt1.date from
(select row_number() over (order by date asc) as rownum,* from table)dt1
left join
(select row_number() over (order by date asc) as rownum,* from table)dt2
on dt1.rownum = dt2.rownum+1 and dt1.mean = dt2.mean
where dt2.mean is null

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-10 : 14:18:01

wow