this sql case has been troubling me for a while and I wanted to ask here what other folks think.

I have a table user who owns vehicles, but the same vehicle maybe owned by multiple user over time, there is another column called effective_date which tells from what day this is owning is effective. Two driver doesn't own the same vehicle, but records are versioned, meaning we can check who owned this vehicle 2 years ago, or 5 years ago using effective date.

Table has following columns,

id, version, name, vehicle_id, effective_date. Every change to this table is versioned

Now there is another table called accidents which tells what accident with vehicle and when, not versioned

it has id, description, vehicle_id, acc_date

Now I am trying to select all accidents and who caused the accident. Inner join doesn't work here, What I do is select all rows from accident table and run sub query for each row and find the user's id and version that was responsible for the cause. This will be super slow and I am looking for more performant way of organizing the date or constructing a query. Right now it runs a subquery for every row it selects from accident table, because each row has different accident date. I am ok doing few queries if there is easy way of doing within a single query.

Example user table

accident:

Result:

thanks for your help

CodePudding user response：

Select a.id as user_id, a.version 
as user_version,
b.id as acc_id, a.vehicle_id,
acc_date from user a
Inner Join
Accident b on
a.vehicle_id = b.vehicle_id

CodePudding user response：

To get the latest user at the time of the accident you can use ROW_NUMBER() sorting by descending effective_date. With this ordering the first user listed for each accident is the responsible one.

For example:

select *
from (
  select *, 
    row_number() over(partition by u.vehicle_id
                      order by effective_date desc) as rn
  from user u
  join accident a on a.vehicle_id = u.vehicle_id
  where u.effective_date <= a.acc_date
) x
where rn = 1