# Thread: Finding the highest current number only for an increasing range

1. Registered User
Join Date
Jan 2012
Posts
2

## Unanswered: Finding the highest current number only for an increasing range

--sorry about the formatting; this is supposed to be two columns!
RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23

The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:

--sorry about the formatting; this is supposed to be three columns!
RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34

The code below is almost there but falls over on Recordno 8:

select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno
)
END as LastAcceleration

From speedtest prev RIGHT JOIN speedtest curr
on prev.vrm = curr.vrm
and prev.recordno+1 = curr.recordno
order by curr.recordno

I think I've been staring at this one too long. I've tried self-joins with correlated sub-queries but think I'm missing something obvious? This is for a 2008 project so doesn't have to work with any versions prior to that. Any help would be appreciated.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```CREATE TABLE #foo (
RecordNo		INT			NOT NULL
,  Speed		INT			NOT NULL
)

INSERT INTO #foo (RecordNo, Speed)
SELECT        1, 0
UNION SELECT  2, 0
UNION SELECT  3, 0
UNION SELECT  4, 0
UNION SELECT  5, 23
UNION SELECT  6, 66
UNION SELECT  7, 48
UNION SELECT  8, 0
UNION SELECT  9, 31
UNION SELECT 10, 0
UNION SELECT 11, 34
UNION SELECT 12, 23

SELECT
this.RecordNo
,  this.Speed
,  CASE
WHEN 0 = prev.Speed THEN this.Speed
WHEN prev.Speed IS NULL THEN 0
ELSE prev.Speed
END AS [Last Acceleration]
FROM #foo AS this
LEFT JOIN #foo AS prev
ON (prev.RecordNo = this.RecordNo - 1)

DROP TABLE #foo```
-PatP

3. Registered User
Join Date
Jan 2012
Posts
2

## Finding the highest current number only for an increasing range

Hi Pat

Thank you for giving up your time to help me out. Unfortunately your solution doesn't quite match the resultset that I'm after. The LastAcceleration for RecordNo 6 should be 66 and not 23 for example.

4. Registered User
Join Date
Nov 2004
Posts
1,428
I don't understand the business logic.
Code:
```RecordNo	Speed	LastAcceleration
4		0	0	-- no speed => 0, OK
5		23	23	-- accelerating from speed 0 to 23 => 23, OK
6		66	66	-- accelerating from speed 23 to 66 => 43, NOK
7		48	66	-- decelerating from speed 66 to 48 => -18, NOK
8		0	66	-- decelerating from speed 48 to 0 => -48, NOK```
What's the logic behind the results you expect?

5. Registered User
Join Date
Jan 2012
Posts
84
Code:
```go
with src as (
SELECT        1 recordno, 0 speed
UNION SELECT  2, 0
UNION SELECT  3, 0
UNION SELECT  4, 0
UNION SELECT  5, 23
UNION SELECT  6, 66
UNION SELECT  7, 48
UNION SELECT  8, 0
UNION SELECT  9, 31
UNION SELECT 10, 0
UNION SELECT 11, 34
UNION SELECT 12, 23
),
brumba( rn, speed, maxspeed )
as (
select recordno, speed, speed
from src where recordno = 1
union all
select f.RecordNo, f.speed,
case when f.Speed > b.speed
then f.Speed
else b.maxspeed
end
from src f join brumba b on f.RecordNo = b.rn + 1
)
select * from brumba;

rn          speed       maxspeed
----------- ----------- -----------
1           0           0
2           0           0
3           0           0
4           0           0
5           23          23
6           66          66
7           48          66
8           0           66
9           31          31
10          0           31
11          34          34
12          23          34

(12 row(s) affected)```