CODEWITHSUNDEEP
sqlpostgresql
Erick David Rodriguez-Orduna
Erick David Rodriguez-Orduna

Reputation: 25

Return lag value when a condition is met in SQL

I have a database filled automatically every 5 minutes but every day I have one hour with samples equal to zero, like this:

Time Power
11:50 1800
11:55 1500
00:00 0
00:05 0
... ...
1:00 1600

And I want the query to be:

Time Power
11:50 1800
11:55 1500
00:00 1500
00:05 1500
... ...
1:00 1600

How can I achieve this? I have read about the Coalesce but dont understand how to add the subquery for 0 value.

Thanks in Advance

Upvotes: 0

Views: 168

Answers (1)

John K.
John K.

Reputation: 525

This should do the trick:

SELECT
    Time,
    CASE
        WHEN Power <> 0 THEN Power
        ELSE ( -- need last nonzero Power
            SELECT
                Power
            FROM
                TableName X -- alias to avoid namespace confusion
            WHERE
                Power <> 0
                AND Time <= TableName.Time
            ORDER BY
                Time DESC
            LIMIT 1
        ) 
    END AS Power
FROM
    TableName
ORDER BY
    Time
;

The subquery grabs the most recent nonzero power. I added the CASE statement for performance reasons: you don't want subqueries to run unless they need to.

Upvotes: 1

Related Questions