CODEWITHSUNDEEP
sql-serverpartitioningrankingrow-number
Matik
Matik

Reputation: 25

Restarting numbering within partition once value change

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

Upvotes: 1

Views: 77

Answers (2)

Gaurav Rajput
Gaurav Rajput

Reputation: 647

Try below query, this will give you the exact required output as described in your question.

DECLARE @SAMPLEDATA TABLE(READYSIGNAL INT,TIMESTAMPP DATETIME)
INSERT INTO @SAMPLEDATA VALUES
(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:09'),
(4,'2017-03-17 17:58:09')
;WITH SAMPLEDATA
AS
(
SELECT *,1 COL FROM (SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 100))SNO,*
 FROM @SAMPLEDATA)T WHERE SNO=1
UNION ALL
SELECT T2.SNO,T2.READYSIGNAL,T2.TIMESTAMPP,
    CASE WHEN T1.READYSIGNAL=T2.READYSIGNAL THEN T1.COL ELSE T1.COL+1 END
        FROM SAMPLEDATA T1 JOIN (SELECT ROW_NUMBER()OVER(ORDER BY (SELECT     100))SNO,*
 FROM @SAMPLEDATA) T2 ON T1.SNO=T2.SNO-1
)
SELECT READYSIGNAL,TIMESTAMPP FROM
    (SELECT READYSIGNAL,MAX(TIMESTAMPP)TIMESTAMPP,MAX(SNO)SNO FROM     SAMPLEDATA     GROUP BY COL,READYSIGNAL)RESULT ORDER BY SNO

OUTPUT

------------------------------------------
--READYSIGNAL   TIMESTAMPP
------------------------------------------
    4           2017-03-17 17:58:25.000
    0           2017-03-17 17:58:22.000
    4           2017-03-17 17:58:18.000
    0           2017-03-17 17:58:10.000
    4           2017-03-17 17:58:09.000
-----------------------------------------

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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)

Upvotes: 1

Related Questions