declare @t table (time_key int not null primary key, val int not null)

-- easy case.  here the last max is just the time_key 
-- when val=max(val), because there is only one max
insert @t (time_key,val) values (1,100)
insert @t (time_key,val) values (2,101)
insert @t (time_key,val) values (3,106)
insert @t (time_key,val) values (4,120)
insert @t (time_key,val) values (5,130)
insert @t (time_key,val) values (6,140) -- this is the first and last max, so desired result is time_key=6
insert @t (time_key,val) values (7,130)
insert @t (time_key,val) values (8,120)
insert @t (time_key,val) values (9,110)
insert @t (time_key,val) values (10,100)
insert @t (time_key,val) values (11,90)

select * from @t
go

declare @t table (time_key int not null primary key, val int not null)

-- hard case - here the time for the last max is time_key=13, because that's the time where afterwards, val never increased.
insert @t (time_key,val) values (1,100)
insert @t (time_key,val) values (2,101)
insert @t (time_key,val) values (3,130) -- first max
insert @t (time_key,val) values (4,120)
insert @t (time_key,val) values (5,130)
insert @t (time_key,val) values (6,140)
insert @t (time_key,val) values (7,150) -- second max 
insert @t (time_key,val) values (8,140)
insert @t (time_key,val) values (9,130)
insert @t (time_key,val) values (10,120)
insert @t (time_key,val) values (11,110)
insert @t (time_key,val) values (12,100)
insert @t (time_key,val) values (13,110) -- this is the last max, so the desired result is time_key=13
insert @t (time_key,val) values (14,100)
insert @t (time_key,val) values (15,90)
insert @t (time_key,val) values (16,80)

select * from @t
go

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY time_key) AS rn,time_key,val
FROM @t
)

SELECT TOP 1 c1.time_key
FROM CTE c1
INNER JOIN CTE c2
ON c2.Rn = c1.Rn-1
INNER JOIN CTE c3
ON c3.Rn = c1.Rn + 1
WHERE c1.val > c2.Val 
AND c1.val > c3.Val
ORDER BY c1.time_key DESC

quote:

---

Originally posted by Lamprey

Can you describe the logic in words? How do you define/determine a "max?"

---

;with CTE
as
(
    select row_number() over (order by time_key) as rn,time_key,val
    from @t
)
select c1.time_key
from CTE c1
left join CTE c2 on c2.Rn = c1.Rn-1 -- match on previous row
left join CTE c3 on c3.Rn = c1.Rn+1 -- match on next row
where 1=1
and (c1.val > c2.Val or c2.val is null)
and (c1.val > c3.Val or c3.val is null)
order by c1.time_key desc