Take the last entry of each row when Flag is a specific value in SQL

Question

I have a data source that looks something like this:

Signal Value	Power value
1	-100
2	99
3	95
4	-64
5	-55
6	92
7	-80
8	23
9	-66
10	-56
11	-46

The signal values are actually random, not ordered like in this example table. I need to query the last value for each of the times when the power is negative.

To try to get this done, I have created a flag column that outputs 1 when the power is negative and zero when it is positive, but I am stuck there. The expected result would be:

Signal Value	Power Value	Flag
1	-100	1
5	-55	1
7	-80	1
11	-46	1

As you can see, basically I think a sort of query that distinguishes each time the flag is 1 before being 0 again and selects that row is what I need to do, although if this is the wrong approach and you think there is an easier or better way to do this please let me know.

Answer 1

With your example data:

create table #data
(
    [Signal Value] int,
    [Power Value] int
)

insert into #data values
(1,-100),
(2,99),
(3,95),
(4,-64),
(5,-55),
(6,92),
(7,-80),
(8,23),
(9,-66),
(10,-56),
(11,-46);

You can achieve this with a window function:

with cte as
(
    select
        *,
        lag([Power Value]) over (order by [Signal Value]) as lastPower,
        case when 
            (lead([Power Value]) over (order by [Signal Value]) > 0 and [Power Value] < 0)
        or ([Power Value] < 0 and [Signal Value] = (select max([Signal Value]) from #data))
        then 1 else 0 end as desiredRow
    from #data
)
select [Signal Value], [Power Value] from cte where desiredRow = 1

There's probably a more elegant way to do this without the complex case statement to check for a change in positive/negative and the extra clause to handle when the last record is negative, but this works.

Results:

Signal Value	Power Value
1	-100
5	-55
7	-80
11	-46