Query to sort through history table

join;

enter image description here

I have the above tables and want a query that will give the results as above.

Basically I have a table of history where I want to display single row by adding two column that show prior Group from current Group in table A. In some cases, there will be multiple lines of history, so I just need to return the latest Finished date where not null.

Best Answer

This will return all current TableA with previous Group info.

Select *,
         (Select top 1 B
          From TableB
          Where started < b.started
          Order by started desc) as PriorGroup,
         (Select top 1 Started
          From TableB
          Where started < b.started
          Order by started desc) as PriorGroupStarted,
From TableA a
Join TableB b on (b.id=a.id and b.finished is null)

Replace top 1 with limit 1 or equivalent for your rdbms.