how to order a series of date column over a row in redshift?

I have this table where there are so many date column in the table.

row  date_a       date_b      date_c      date_d     date_x
1   2023-07-18  2023-07-19  2023-07-22  2023-07-24   2023-07-20
2   2023-07-17  2023-07-17  2023-07-19  2023-07-23   2023-07-21

First, I want to order these series of date column based on the earliest

Second, I want to get the date that comes right before date_x

so the expected results:

row  date_a       date_b      date_c      date_d     date_x       date_before_x   status
1   2023-07-18  2023-07-19  2023-07-22  2023-07-24   2023-07-20    2023-07-19       b
2   2023-07-17  2023-07-17  2023-07-19  2023-07-23   2023-07-21    2023-07-19       c

is someone can help me how can I get the expected result in redshift?

thank you in advance.

Solution

This isn't a window function problem. Windows are useful when comparing values between rows, not values within a row. The function you need is LEAST(). Like this:

with days_before as (
  select *,
    case when date_a < date_x then date_x - date_a end as diff_a,
    case when date_b < date_x then date_x - date_b end as diff_b,
    case when date_c < date_x then date_x - date_c end as diff_c,
    case when date_d < date_x then date_x - date_d end as diff_d
  from test)
select date_a, date_b, date_c, date_d,
  date_x - least(diff_a, diff_b, diff_c, diff_d) as date_before_x
from days_before

I prefer to use DECODE and not CASE in this type of code but I developed this on a postgres fiddle and decode isn't supported on postgres. "decode(date_c < date_x, true, date_x - date_a)". I believe all these date operations work the same in Redshift but some tweaking might be needed. Fiddle - http://sqlfiddle.com/#!17/410d8/8