Help with SQL queries - 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)

Help with SQL queries

Author

Topic

samarjit_r
Starting Member

2 Posts

Posted - 2011-05-15 : 01:19:21

I have a table with data in following format

Time       Event

10:10 AM       1
10:15 AM       2
10:12 AM       1
10:16 AM       2
10:18 AM       1
10:17 AM    2

I am looking for a sql query that will return me rows whenever there is a change in event when its ordered by time
Once ordered by time the records will be:

Time       Event

10:10 AM       1
10:12 AM       1
10:15 AM       2
10:16 AM       2
10:17 AM    2
10:18 AM       1

Expected output to be:
Time       Event

10:12 AM       1
10:15 AM       2
10:17 AM    2
10:18 AM       1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-15 : 02:21:10

[code]
; with
data as
(
select *, row_no = row_number() over (order by Time)
from a_table
)
select c.Time, c.Event
from data c
left join data p on c.row_no = p.row_no + 1
left join data n on c.row_no = n.row_no - 1
where c.Event <> p.Event
or c.Event <> n.Event
order by c.Time
[/code]

KH
[spoiler]Time is always against us[/spoiler]

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-15 : 16:47:47

I think using a self join may be enough ....

; with
data as
(
select *, row_no = row_number() over (order by Time)
from a_table
)

select distinct c.*
from data c join data p
on c.Event<>p.Event and (c.row_no=p.row_no+1 or c.row_no=p.row_no-1)
order by c.row_no

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources