I'd like to ask your assistance in writing some T-

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

Transact-SQL (2005)

I'd like to ask your assistance in writing some T-

Author

Topic

Gold999
Starting Member

6 Posts

Posted - 2008-03-19 : 18:37:14

Dear friends!
I'd like to ask your assistance in writing some T-SQL.
I have a table

MYTABLE (THEDATE datetime, TEMPERATURE numeric(4))

3.02 15
4.02 16
5.02 16
8.02 13
10.02 15
11.02 15
12.02 19

I need a T-SQL without using analitic(range) functions like RANK or DENSE-RANK to provide the following output:

3.02 15
4.02 16
8.02 13
10.02 15
12.02 19

In other words I need to obtain only first occurance of the "neibough" adjacent rows where the temperature is equal.

Please advice.
Thanks in advance.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-19 : 18:52:42

wouldn't a simple GROUP BY do it?

select min(thedate) as thedate, temperature from mytable group by temperature order by min(thedate)

Be One with the Optimizer
TG

Gold999
Starting Member

6 Posts

Posted - 2008-03-19 : 18:59:45

quote:
Originally posted by TG

wouldn't a simple GROUP BY do it?

select min(thedate) as thedate, temperature from mytable group by temperature order by min(thedate)

Be One with the Optimizer
TG

not exactly.
Your solution will provide a single temperature value per specific date. It means that output will contain temperature 15 only once.
I need in case adjacent rows have the same temperature to output the earliest, but in case this temperature will be later for some another date I'd like to obtain it either.
The output I'd like is:

3.02 15
4.02 16
8.02 13
10.02 15
12.02 19

and not

3.02 15
4.02 16
8.02 13
12.02 19

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-20 : 00:43:59

Ok - I see now.
Well this is a little funky but it seems to work:


create table #t (thedate datetime primary key clustered, temperature int, grp int)

--set up your table
declare @MYTABLE table (THEDATE datetime, TEMPERATURE int)
insert @mytable (thedate, temperature)
select '3/02/2008', 15 union all
select '4/02/2008', 16 union all
select '5/02/2008', 16 union all
select '8/02/2008', 13 union all
select '10/02/2008', 15 union all
select '11/02/2008', 15 union all
select '12/02/2008', 19

insert #t (thedate, temperature)
select thedate, temperature from @myTable

declare @temp int
       ,@grp int

select @temp = -100
       ,@grp = 1

update #t set
       @grp = grp = @grp + case when temperature = @temp then 0 else 1 end
       ,@temp = temperature

select thedate, temperature
from   (
       select thedate, temperature, dense_rank() over (partition by grp order by thedate) dr from #t
       ) d
where  dr = 1

drop table #t

output:

thedate                 temperature
----------------------- -----------
2008-03-02 00:00:00.000 15
2008-04-02 00:00:00.000 16
2008-08-02 00:00:00.000 13
2008-10-02 00:00:00.000 15
2008-12-02 00:00:00.000 19

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources