Foreach/per-item iteration in SQL-CodePudding

I'm new to SQL and I think I must just be missing something, but I can't find any resources on how to do the following:

I have a table with three relevant columns: id, creation_date, latest_id. latest_id refers to the id of another entry (a newer revision).

For each entry, I would like to find the min creation date of all entries with latest_id = this.id. How do I perform this type of iteration in SQL / reference the value of the current row in an iteration?

CodePudding user response：

You could solve this with a loop, but it's not anywhere close the best strategy. Instead, try this:

SELECT tf.id, tf.Creation_Date
FROM 
(
    SELECT t0.id, t1.Creation_Date, 
        row_number() over (partition by t0.id order by t1.creation_date) rn
    FROM [MyTable] t0 -- table prime
    INNER JOIN [MyTable] t1 ON t1.latest_id = t0.id -- table 1
) tf -- table final
WHERE tf.rn = 1

This connects the id to the latest_id by joining the table to itself. Then it uses a windowing function to help identify the smallest Creation_Date for each match.

CodePudding user response：

select 
    t.id, min(t2.creation_date) as min_creation_date
from 
    mytable t 
    left join 
    mytable t2 on t2.latest_id = t.id
group by 
    t.id