Return the second newst record of a group sql

I have a table like the following:

coworker            date
   1             2020.10.30
   1             2019.09.12
   1             2018.05.29
   2             2019.08.07
   3             2020.03.12
   3             2018.06.24
   4             2020.08.16

how can I get this:

coworker            date
   1             2019.09.12
   2             2019.08.07
   3             2018.06.24
   4             2020.08.16

the result should be the second newest record of a coworker if there’s more than one, or the only present

Thanks for any help!

Answer

Try the following with window function row_number. Here is the demo.

select
    coworker,
    date
from
(
    select
        *,
        row_number() over (partition by coworker order by date) as rn,
        count(*) over (partition by coworker) as ttl
    from myTable
) subq
where (ttl > 1 and rn = ttl - 1) or (ttl = 1 and rn = 1);

Output:

| coworker | date       |
| -------- | ---------- |
| 1        | 2019-09-12 |
| 2        | 2019-08-07 |
| 3        | 2018-06-24 |
| 4        | 2020-08-16 |