How can i take the newest record by the date that they have?

Question

In my table, I have some records that are repeated, and I want to take the record that have the newest date. This is because I´m using a cursor, that cursor is returning me the repeated records and that information I have to insert into a table, but I can´t do it because of the repeated records. It maybe be a function error, because I´m using 4 of them, idk. I don´t know what can I do to solve it. Sorry about my english.

This is the procedure that I was trying to use:

CREATE OR REPLACE PROCEDURE PR_INSERT_DATOS IS
CURSOR CUR_DATOS IS
    SELECT DISTINCT NUMRUN AS NUMRUN, PNOMBRE||' '||SNOMBRE||' '||APATERNO||' '||AMATERNO AS NOMBRE
    FROM ANTECEDENTES_PERSONALES
    ORDER BY NUMRUN;
    V_PTJE_ANNOS_EXP NUMBER(8);
    V_PTJE_HORAS_TRAB NUMBER(8);
    V_PTJE_ZONA NUMBER(8);
    V_PTJE_RANKING NUMBER(8);
BEGIN
    EXECUTE IMMEDIATE('TRUNCATE TABLE DETALLE_PUNTAJE_POSTULACION');

        FOR REG_DATOS IN CUR_DATOS LOOP
            V_PTJE_ANNOS_EXP:=FN_PTJ_ANNOS_EXP(REG_DATOS.NUMRUN);
            V_PTJE_HORAS_TRAB:=FN_PTJE_HORAS(REG_DATOS.NUMRUN);
            V_PTJE_ZONA:=FN_PTJE_ZONA(REG_DATOS.NUMRUN);
            V_PTJE_RANKING:=FN_PTJE_RANKING(REG_DATOS.NUMRUN);
            INSERT INTO DETALLE_PUNTAJE_POSTULACION VALUES (REG_DATOS.NUMRUN, REG_DATOS.NOMBRE, V_PTJE_ANNOS_EXP,
            V_PTJE_HORAS_TRAB, V_PTJE_ZONA, V_PTJE_RANKING, 0, 0);
            END LOOP;

END;

And that's the error that I have:

01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

Does this answer your question? [Oracle SQL query: Retrieve latest values per group based on time](https://stackoverflow.com/questions/2000908/oracle-sql-query-retrieve-latest-values-per-group-based-on-time) — astentx, Jul 10 '23 at 07:13

score 0 · Answer 1 · answered Jul 10 '23 at 06:17

You could use a solution like using an inner query as shown below

SELECT OTHER_COLS,MAX(DATE_COL) AS DATE_COL FROM SCHEMA.TABLE_NAME
GROUP BY OTHER_COLS

and use this to inner join with the same table on OTHER_COLS AND DATE_COL to fetch the columns you want to insert into your second table

score 0 · Answer 2 · answered Jul 10 '23 at 06:25

Code you posted can't return TOO_MANY_ROWS, so I suspect it is about (at least) one of functions you're calling. You should

carefully check error message; what does it exactly say? Which procedure/function did raise that error, and in which line? Oracle displays that info
fix it so that it doesn't raise the error

As of the way to fetch rows that belong to the last date, there are several ways to do it.

Let's presume your table looks like this (I'm setting date format so that you'd know what it represents; you don't have to do that):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select * from your_table order by nombre, datum desc;

    NOMBRE DATUM
---------- ----------
         1 10.07.2023  --> last date for NOMBRE = 1
         1 21.06.2023
         2 13.02.2023  --> last (and only) date for NOMBRE = 2

One option is to use a simple max aggregate function:

SQL> select nombre, max(datum) datum from your_table group by nombre;

    NOMBRE DATUM
---------- ----------
         1 10.07.2023
         2 13.02.2023

This should be enough for what you're doing right now.

Though, if you - for example - wanted to use the 2nd highest date value, note that there are analytic functions you could use, such as row_number - partition rows per nombre, sort them by datum in descending order and fetch rows that ranked as the highest (rn = 1):

SQL> with temp as
  2    (select nombre, datum,
  3       row_number() over (partition by nombre order by datum desc) rn
  4     from your_table
  5    )
  6  select nombre, datum
  7  from temp
  8  where rn = 1;

    NOMBRE DATUM
---------- ----------
         1 10.07.2023
         2 13.02.2023

If you wanted to take the 2nd highest, you'd modify line #8 to rn = 2

Another analytic function which might be useful is rank - if there were more rows with the same datum value, you'd get all of them as result. That's most probably not the case in your situation.

score 0 · Answer 3 · answered Jul 10 '23 at 07:42

You do not need a cursor or loops and can do it in a single SQL statement:

CREATE OR REPLACE PROCEDURE PR_INSERT_DATOS
IS
BEGIN
  DELETE FROM DETALLE_PUNTAJE_POSTULACION;

  INSERT INTO DETALLE_PUNTAJE_POSTULACION
  SELECT numrun,
         nombre,
         FN_PTJ_ANNOS_EXP(NUMRUN) AS annos_exp,
         FN_PTJE_HORAS(NUMRUN) AS horas,
         FN_PTJE_ZONA(NUMRUN) AS zona,
         FN_PTJE_RANKING(NUMRUN) AS ranking,
         0,
         0
  FROM   (
    SELECT numrun,
           PNOMBRE||' '||SNOMBRE||' '||APATERNO||' '||AMATERNO AS nombre,
           ROW_NUMBER() OVER (
             PARTITION BY columns_you_want_to_group_by
             ORDER BY your_date_column DESC
           ) AS rn
    FROM   ANTECEDENTES_PERSONALES
  )
  WHERE  rn = 1;
END;
/

You have not stated which columns you wish to group by or what the name of the date column is that you wish to take the latest value so the PARTITION BY and ORDER BY clauses are left for you to complete.

How can i take the newest record by the date that they have?

3 Answers3