Row_number in SQL

Question

I have this table "triggerTable"

trigger	date	isError
a	2021-02-16 04:08:12.810	0
b	2021-01-31 04:09:22.977	1
c	2021-02-10 04:15:34.740	1
c	2021-02-16 04:16:06.037	0
d	2021-02-12 04:15:34.740	0
d	2021-02-13 04:16:06.037	1
e	2021-01-24 04:15:34.740	0
e	2021-01-31 04:16:06.037	0

I need to have for each trigger the latest date and if isError(1) or not isError(0). If the trigger has failed (even NOT recently) I need to have that row.

How can i have this output in SQL Script?

trigger	date	isError
a	2021-02-16 04:08:12.810	0
b	2021-01-31 04:09:22.977	1
c	2021-02-10 04:15:34.740	1
d	2021-02-13 04:16:06.037	1
e	2021-01-31 04:16:06.037	0

Thanks

Answer 1

You can do this with conditional aggregation:

select trigger,
       coalesce(case when isError = 1 then date end,
                max(date)
               ) as date,
       max(isError) as isError
from t
group by trigger;

Answer 2

Use MAX() and FIRST_VALUE() window functions:

SELECT DISTINCT trigger,
       MAX(date) OVER (PARTITION BY trigger) date,
       FIRST_VALUE(isError) OVER (PARTITION BY trigger ORDER BY date DESC) isError
FROM triggerTable

See the demo.
Results:

trigger	date	isError
a	2021-02-16 04:08:12.810	0
b	2021-01-31 04:09:22.977	1
c	2021-02-16 04:16:06.037	0
d	2021-02-13 04:16:06.037	1
e	2021-01-31 04:16:06.037	0

Answer 3

Use ROW_NUMBER with an appropriate ORDER BY clause:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY "trigger"
                                   ORDER BY isError DESC, date DESC) rn
    FROM triggerTable t
)

SELECT "trigger", date, isError
FROM cte
WHERE rn = 1;

Demo

The logic above will first attempt to return the latest record in error. Should no records be in error, it would then default to returning the latest record not in error.