Search code examples
sqloracle-databaselinked-listranking

Linking Rows in SQL Based on Sorted Column Value

I have a database table that contains a list of "statuses" for donators. People who have donated $5,000 have a certain rank, and the same for $10,000, $50,000, etc. For the sake of convenience in various queries, I would like to have each status point to the next highest status (unless it is the highest). Like so:

ID    Amount    Next ID
1     $5,000    2
2     $10,000   3
3     $50,000   Null

I would like to automatically update the Next ID column with a trigger, whenever a new row is inserted, or the Amount column edited during an update query, or a row is deleted.

I'm building this for Oracle 9i, so it's no problem to make a trigger that will do the updating--the only question is, how?

I'm struggling with the concept of doing a SELECT to get the whole table, with an ORDER BY on the Amount to sequence it, and then somehow using this to do the update. It seems like it should be possible, but I haven't figured it out yet.

Also, I'm sure that some sort of PL/SQL loop might accomplish this.

Any ideas?

Solution

  • Make the Amount the PK

    For example, the table is empty. Then the amount 10k is inserted:

    Amount    Next ID
$10,000   null

    Then the value 5k is inserted: find the first value > 5k, which is 10k, so use the 10k as the nextId:

    Amount    Next ID
$5,000    10,000
$10,000   null

    Next, the value 7k is inserted: find the first value > 7k, which is 10k, so use 10k as the nextId:

    Amount    Next ID
$5,000    10,000
$7,000    10,000
$10,000   null

    But now the value of 5k needs to be adjusted: find the max value < 7k and set the nextId as 7k:

    Amount    Next ID
$5,000    7,000
$7,000    10,000
$10,000   null

    So, generalizing, the algorithm should be:

    For any new value inserted:

    1 - find the min value in the table > value being inserted and set the next id of the value being inserted as the min value in the table.

    2 - find the max value in the table < value being inserted and set the next id of the max value in the table as the value being inserted.

    Deleting a row:

    If the table is as this:

    Amount    Next ID
$5,000    7,000
$7,000    10,000
$10,000   null

    and we want to delete the row 7,000 then

    1 - Get the next id for the row being deleted. In this case it is 10k.

    2 - Get the row with the next id 7k. In this case the row is the one with 5k.

    3 - Set the row found in the step 2 with the next id found in the step 1.

    Amount    Next ID
$5,000    10,000
$7,000    10,000
$10,000   null

    4 - Delete the row with 7k:

    Amount    Next ID
$5,000    10,000
$10,000   null