Group By of 1 Oracle SQL Column with latest date

Question

I have a table (TVIC_ODS_MVEL_PRQE), in ORACLE SQL, with millions of records and need to extract from it only 2 columns: phone number (NU_TLFN) and date (DT_PRMR_ATVC_LNHA). The problem is that I only need phone numbers without duplicates and only the latest date. Only there are duplicate values in both columns, for example:

|  NU_TLFN  | DT_PRMR_ATVC_LNHA |
|11911111111|     24/08/2021    |
|11911111111|     24/08/2021    |
|11911111111|     25/08/2021    |
|11922222222|     21/08/2021    |
|11922222222|     22/08/2021    |
|11922222222|     22/08/2021    |

And I need a select that brings me only 1 record per phone number with the latest date. Example of the expected result:

|  NU_TLFN  | DT_PRMR_ATVC_LNHA |
|11911111111|     25/08/2021    |
|11922222222|     22/08/2021    |

I tried to select that way:

select distinct NU_TLFN, DT_PRMR_ATVC_LNHA from TVIC_ODS_MVEL_PRQE
where NU_ANO_MES in ('202105','202106','202107','202108')
group by NU_TLFN
order by DT_PRMR_ATVC_LNHA desc

Where being the restricted of the analysis (as of May) with column NU_ANO_MES showing the months.

But select gives error in Group By and only runs if I put group by NU_TLFN, DT_PRMR_ATVC_LNHA. But having duplicates in both columns does not solve the problem.

Any idea how to solve?

PS: Unfortunately there is no column id in the table

Answer 1

"But select gives error in Group By and only runs if I put group by NU_TLFN" extamtente. When using GROUP BY all fields of SELECT shall be grouped or use aggregation functions such as SUM and MAX.

Other than MySQL which accepts fields outside the GROUP BY, in the Oracle is mandatory.

You can then group by "NU_TLFN", to remove duplicates, and use MAX(DT_PRMR_ATVC_LNHA) to pick up the latest date, so:

select NU_TLFN, MAX(DT_PRMR_ATVC_LNHA)
  from TVIC_ODS_MVEL_PRQE
 group by NU_TLFN;

You can see it working here: http://sqlfiddle.com/