I have a table like this in Oracle SQL developer but with multiples id. Every id has a row for each month

How to i select, per id, only the line where the date column is equal to the max value across columns date date became A, date date became B, date date became C? I was trying to use the GREATEST() but it seens to considere the column with the NULL the greatest one

CodePudding user response：

You need to craft the ordering very carefully to correctly deal with nulls.

You can do:

select *
from (
  select t.*,
    row_number() over(
      partition by id
      order by case when a > b then case when c > a then c else a end
                    when a < b then case when c > b then c else b end
                    else c end DESC
    ) as rn
  from t
) x
where rn = 1

CodePudding user response：

Read comments within code.

Some sample data:

SQL> with
  2  test (datum, datum_a, datum_b, datum_c, id) as
  3    (select date '2017-10-01', date '2018-03-01', date '2017-10-01', null, 1 from dual union all
  4     select date '2017-11-01', date '2018-03-01', date '2017-10-01', null, 1 from dual union all
  5     select date '2018-03-01', date '2018-03-01', date '2017-10-01', null, 1 from dual
  6    ),

This (01-jan-1900) is used as the lowest date you can have. It is then used in the NVL function, to avoid NULL problem with the GREATEST function. I guess that's better than just hardcoding it

  7  absolute_min_date (datum) as
  8    (select date '1900-01-01' from dual),

Find max datum (of all 4 date columns) per each id

  9  max_datum_per_id as
 10    (select t.id, max(greatest(nvl(t.datum  , a.datum),
 11                               nvl(t.datum_a, a.datum),
 12                               nvl(t.datum_b, a.datum),
 13                               nvl(t.datum_c, a.datum)
 14                             )) max_overall_datum
 15     from test t cross join absolute_min_date a
 16     group by t.id
 17    )

Finally, take row (from the "original" table) whose ID and datum match values from the previous CTE

 18  select t.*
 19  from test t join max_datum_per_id m on m.id = t.id and m.max_overall_datum = t.datum;

DATUM     DATUM_A   DATUM_B   DATUM_C            ID
--------- --------- --------- ---------- ----------
01-mar-18 01-mar-18 01-oct-17                     1

SQL>

At the end, I'm not sure whether you want MAX (which goes along with the GREATEST function) or MIN (so you'd use LEAST) because your title/message text/comments differ from each other.