MAX function to select the result with the latest date

Question

I am trying to select the line (or maybe multiple lines) with the latest date.

Reference to the answer in the thread below. I tried three methods and none is working in the case. Oracle Sql: Select only the latest records by an id and a date

For example, use the most common solution I have something like this.

SELECT ec.dat_cre
FROM sat_hecc ec
WHERE ec.dat_cre=(select max(ec1.dat_cre) from sat_hecc ec1 where ec1.num_int_hcc = ec.num_int_hcc )

However, the result below shows the MAX function isn't working.

Where is the problem? Thank you for your input in advance.

Answer 1

Of course MAX is working; I'd say that it is you, who can't interpret result you got correctly.

Sample table; asterisks mark MAX date per each num_int_hcc:

SQL> create table sat_ecc (num_int_hcc, dat_Cre) as
  2    (select 1, date '2024-02-04' from dual union all  -- *
  3     select 1, date '2024-02-03' from dual union all
  4     --
  5     select 2, date '2024-01-31' from dual union all  -- *
  6     --
  7     select 3, date '2024-01-24' from dual union all  -- *
  8     select 3, date '2024-01-20' from dual union all
  9     select 3, date '2024-01-10' from dual
 10    );

Table created.

This is your "original" query:

SQL> select ec.dat_cre
  2  from sat_ecc ec
  3  where ec.dat_cre = (select max(ec1.dat_cre)
  4                      from sat_ecc ec1
  5                      where ec1.num_int_hcc = ec.num_int_hcc
  6                     );

DAT_CRE
----------
04.02.2024
31.01.2024
24.01.2024

If you expand it so that it returns num_int_hcc as well, things are much more clear, i.e. query returns rows that correspond to MAX dat_cre for each num_int_hcc! That's what where clause in subquery does:

SQL> select ec.num_int_hcc, ec.dat_cre
  2  from sat_ecc ec
  3  where ec.dat_cre = (select max(ec1.dat_cre)
  4                      from sat_ecc ec1
  5                      where ec1.num_int_hcc = ec.num_int_hcc
  6                     );

NUM_INT_HCC DAT_CRE
----------- ----------
          1 04.02.2024
          2 31.01.2024
          3 24.01.2024

If you want to return row that corresponds to MAX dat_cre, period, then remove that where clause:

SQL> select ec.num_int_hcc, ec.dat_cre
  2  from sat_ecc ec
  3  where ec.dat_cre = (select max(ec1.dat_cre)
  4                      from sat_ecc ec1
  5                     );

NUM_INT_HCC DAT_CRE
----------- ----------
          1 04.02.2024

SQL>

---

On the other hand, if you want to avoid scanning the table twice (in subquery (to find MAX value) and main query (to get desired result)), consider switching to a CTE (or a subquery) which utilizes RANK analytic function:

SQL> with temp as
  2    (select ec.num_int_hcc,
  3            ec.dat_cre,
  4            rank() over (order by ec.dat_cre desc) rnk
  5     from sat_ecc ec
  6    )
  7  select num_int_hcc, dat_cre
  8  from temp
  9  where rnk = 1;

NUM_INT_HCC DAT_CRE
----------- ----------
          1 04.02.2024

SQL>