Trying to select max date out of rows with duplicated ID

Question

I'm trying to select max(date) out of rows with duplicated ID with the following SQL

SELECT
ER.ID_PROC_VERSION,
P.ID || ' / ' || PV.COD_PROCEDIMIENTO || ' / ' || P.COD_SIA COD_GC_IPSC_SIA,
PV.DENOMINACION DENOMINACION,

ER.TIPO_ENVIO TIPO_ENVIO,
ER.RESULTADO_ENVIO RESULTADO_ENVIO,
ER.FH_ENVIO FH_ENVIO

FROM ENVIO_REC ER

INNER JOIN PROCEDIMIENTO_VERSION PV
ON ER.ID_PROC_VERSION = PV.ID
INNER JOIN PROCEDIMIENTO P
ON PV.ID_PROCEDIMIENTO = P.ID

WHERE
(ER.ID_PROC_VERSION, ER.FH_ENVIO) IN (SELECT ID_PROC_VERSION, MAX(FH_ENVIO) FROM ENVIO_REC GROUP BY ID_PROC_VERSION)

The problem is that the rows with duplicated ID are getting removed instead of getting just the one with the max. date. What do you think I could improve? Thanks for your time

Answer 1

To me, it looks as if row_number (or rank, perhaps?) analytic function would help. Partitioned by each id_proc_version, it returns 1 for the "highest" fh_envio value, so all you have to do is to fetch rows that rank as highest.

Something like this:

WITH
   temp
   AS
      (SELECT er.id_proc_version,
              p.id || ' / ' || pv.cod_procedimiento || ' / ' || p.cod_sia as cod_gc_ipsc_sia,
              pv.denominacion,
              er.tipo_envio,
              er.resultado_envio,
              er.fh_envio,
              --
              ROW_NUMBER ()
                 OVER (PARTITION BY er.id_proc_version
                       ORDER BY er.fh_envio DESC)
                 rn
         FROM envio_rec er
              INNER JOIN procedimiento_version pv
                 ON er.id_proc_version = pv.id
              INNER JOIN procedimiento p ON pv.id_procedimiento = p.id)
SELECT t.*
  FROM temp t
 WHERE t.rn = 1;

---

(BTW, there's no point in specifying column aliases which are exactly the same as column names themselves; that - instead of simplifying your code - actually makes it more difficult to read).