Sort a list using loops

I'm still fairly new to PL/SQL and I tried to solve the following problem for a long time on my own, but I can not get to a solution (and i did not find a similar problem solved) hence I thought I ask myself.

I am trying to sort a list by the date (which indicates the 'time_left_to_live') so that the result is the same list but, as you can guess, sorted by the date.

EDIT: Maybe i should be even more precise:

My original goal is to write an AFTER UPDATE TRIGGER, which sorts the table again if the time_left_to_live is updated. My idea was to write a Procedure which sorts (or more updates) the list and call it.

Example for clarification:

UPDATE testv1 SET time_left_to_live = '01.05.2020' WHERE list_id = 3;

so after that the old data of list_id 3 should be 1 since it has the shortest amount left to live and the other datas should be incremented by 1.

1     01.05.2020
2     03.05.2020
3     31.05.2020

I hope I could explain the situation somewhat good.

I tried it with a nested loop but I simply can't think inside. Any help is appreciated.

My table:

DROP TABLE testv1;
DROP PROCEDURE wlp_sort;

CREATE TABLE testv1(
list_ID INT,
time_left_to_live DATE
);

INSERT INTO testv1 VALUES (3, '01.06.2020');
INSERT INTO testv1 VALUES (2, '31.05.2020');
INSERT INTO testv1 VALUES (1, '03.05.2020');
--UPDATE testv1 SET time_left_to_live = '01.05.2020' WHERE list_ID = 2;
SELECT * FROM testv1;

Solution

I'd suggest you not to do that. I presume that table you posted as an example is exactly that - an example. In reality, it is more complex. Furthermore, I presume that list_id column identifies each row which is OK, but wrong to be used for sorting purposes, especially not the way you wanted - by updating its value via database triggers.

What should you do, then? Use ORDER BY as it is the only mechanism that guarantees that rows will be returned in desired order. A long time ago, I think the last Oracle database version was 8i, you could use group by clause which in the background sorted result for you, but those times have passed so - order by it is.

Here's an example of what I mean.

This is the original contents of your table. Query includes additional column - the one you might want to use - row_number analytic function which "calculates" ordinal number on-the-fly. At first, it matches list_id value.

SQL> select list_id,
  2         time_left_to_live,
  3         row_number() over (order by time_left_to_live) rn
  4  from testv1
  5  order by time_left_to_live;

   LIST_ID TIME_LEFT_         RN
---------- ---------- ----------
         1 03.05.2020          1
         2 31.05.2020          2
         3 01.06.2020          3

SQL>

Let's update one row (also from your example) and see what happens (note that I used date literal; you updated a date column with a string. Oracle did implicitly try - and succeed - to convert it to a valid date value, but you shouldn't rely on that. 01.05.2020 could be 1st of May or 5th of January, depending on date format which may change and might be different in different databases. Date literal is, on the other hand, always in format date 'yyyy-mm-dd' and there's no confusion):

SQL> update testv1 set time_left_to_live = date '2020-05-01' where list_id = 2;

1 row updated.

SQL> select list_id,
  2         time_left_to_live,
  3         row_number() over (order by time_left_to_live) rn
  4  from testv1
  5  order by time_left_to_live;

   LIST_ID TIME_LEFT_         RN
---------- ---------- ----------
         2 01.05.2020          1
         1 03.05.2020          2
         3 01.06.2020          3

SQL>

ORDER BY clause sorts data, but now list_id and rn are different; list_id didn't change, but rn represents new order.

If your next step is to do something with a row whose ordinal number is 1, you'd just use query I suggested as an inline view and fetch values whose rn = 1:

SQL> select list_id,
  2         time_left_to_live
  3  from (select list_id,
  4               time_left_to_live,
  5               row_number() over (order by time_left_to_live) rn
  6        from testv1
  7       )
  8  where rn = 1;

   LIST_ID TIME_LEFT_
---------- ----------
         2 01.05.2020

SQL>

I suggest you use this option.

Additional drawbacks related to database trigger: if you write an update statement that modifies list_id, no problem - it works outside of trigger and list_id and rn are synchronized again:

SQL> update testv1 a set
  2    a.list_id = (select x.rn
  3                 from (select b.list_id,
  4                              b.time_left_to_live,
  5                              row_number() over (order by b.time_left_to_live) rn
  6                       from testv1 b
  7                      ) x
  8                 where x.list_id = a.list_id
  9                );

3 rows updated.

SQL> select list_id,
  2         time_left_to_live,
  3         row_number() over (order by time_left_to_live) rn
  4  from testv1
  5  order by time_left_to_live;

   LIST_ID TIME_LEFT_         RN
---------- ---------- ----------
         1 01.05.2020          1
         2 03.05.2020          2
         3 01.06.2020          3

SQL>

But, in a trigger, you modify a column which fires a trigger which modifies a column which fires a trigger which modifies a column ... until you exceed maximum number of recursive SQL levels and then Oracle raises an error.

Or, if you planned to select from the table and then do something with it, you'll hit the mutating table error as you can't select from a table which is just being modified. True, you might use a compound trigger (or - in previous Oracle database versions - package and custom type), but - once again, in my opinion, that's just not the way you should handle the problem.