Check in oracle to order

Question

Good morning forum.

I have this basic query in oracle

SELECT P.IDPROYECT, P.NOPROGRAM, P.IDETAP, P.STATUS_ETAP, P.VERSION
FROM poa.PROYECT P
LEFT JOIN DGI.DEPENDENCIA UA ON (P.RAM = UA.RAM AND P.DEPT = UA.DEPT AND UA.YEAR = 2021);

I am attracted to a series of records so the noprogram is repeated 3 times but the only thing different is that the version of those 3 is newer, I would like it to show me only 1 of the 3 records that has a higher version

Answer 1

Use row_number as follows:

select * from
(SELECT P.IDPROYECT, P.NOPROGRAM, P.IDETAP, P.STATUS_ETAP, P.VERSION,
       ROW_NUMBER() OVER (PARTIRION BY P.NOPROGRAM ORDER BY P.VERSION DESC) AS RN
  FROM poa.PROYECT P
  LEFT JOIN DGI.DEPENDENCIA UA
    ON (P.RAM = UA.RAM AND P.DEPT = UA.DEPT AND UA.YEAR = 2021)
) Where rn = 1