I'm struggling with quite simple (I think) topic, which I can not get to run.
An example data:
|ready_signal|timestamp |
|4 |2017-03-17 17:58:25|
|4 |2017-03-17 17:58:24|
|4 |2017-03-17 17:58:23|
|0 |2017-03-17 17:58:22|
|0 |2017-03-17 17:58:21|
|0 |2017-03-17 17:58:19|
|4 |2017-03-17 17:58:18|
|4 |2017-03-17 17:58:15|
|0 |2017-03-17 17:58:10|
|0 |2017-03-17 17:58:09|
|0 |2017-03-17 17:58:04|
|4 |2017-03-17 17:58:03|
Now what I'm trying to achieve is, to get maximal value of timestamp, within every ready_signal change. So the result should look like:
|ready_signal|timestamp |
|4 |2017-03-17 17:58:25|
|0 |2017-03-17 17:58:22|
|4 |2017-03-17 17:58:18|
|0 |2017-03-17 17:58:10|
|4 |2017-03-17 17:58:09|
I was trying with partitioning function, with ROW_NUMBER etc. but without success. I can not partition over none of this columns. Partitioning over ready_signal, will return only two values (and with use of ORDER BY within partition).
I think, somebody definitely have had the same issue. Is like, I would need a single unique partition number, but every time ready_signal value will change.
Sorry, for not posting the example code. This is what I was experimenting with:
SELECT ready,
timestamp,
ROW_NUMBER() OVER(PARTITION BY ready ORDER BY timestamp DESC) AS readyTime
FROM bubu
ORDER BY timestamp DESC
I also tried to pick up some max values:
SELECT ready,
timestamp,
ROW_NUMBER() OVER(PARTITION BY ready ORDER BY timestamp DESC) AS readyTime
FROM bubu
ORDER BY timestamp DESC
This is close but suffers from the issue I have commented on the question - because the timestamps aren't unique, it's possible to get different result sets. In this case, I frequently get another 0 row for 2017-03-17 17:58:09 since it's conceivable that one of them occurred before the 4.
Anyhow, the way to write this is that you want to select the rows that either have no successor or where the successor has a different value for ready_state. Once you re-state the question like that, the code practically writes itself:
declare @t table (ready_signal int,timestamp datetime)
insert into @t(ready_signal,timestamp) values
(4,'2017-03-17T17:58:25'),
(4,'2017-03-17T17:58:24'),
(4,'2017-03-17T17:58:23'),
(0,'2017-03-17T17:58:22'),
(0,'2017-03-17T17:58:21'),
(0,'2017-03-17T17:58:19'),
(4,'2017-03-17T17:58:18'),
(4,'2017-03-17T17:58:15'),
(0,'2017-03-17T17:58:10'),
(0,'2017-03-17T17:58:09'),
(0,'2017-03-17T17:58:09'),
(4,'2017-03-17T17:58:09')
;With Numbered as (
select ready_signal,timestamp,
ROW_NUMBER() OVER (ORDER BY timestamp) as rn
from @t
)
select
t1.ready_signal,t1.timestamp
from
Numbered t1
left join
Numbered t2
on
t1.rn = t2.rn - 1
where
t2.rn is null or --No successor
t2.ready_signal != t1.ready_signal --Successor different
Results:
ready_signal timestamp
------------ -----------------------
0 2017-03-17 17:58:09.000
4 2017-03-17 17:58:09.000
0 2017-03-17 17:58:10.000
4 2017-03-17 17:58:18.000
0 2017-03-17 17:58:22.000
4 2017-03-17 17:58:25.000
(You can add an explicit ORDER BY if the order of the result set is important to you)