Select most recent timestamp per record from multiple joined tables

Question

I want to create a view on multiple joined tables.
Each of the tables has a timestamp.
The different records in the source tables may be updated at different times.

So, in the view I would like to select the most recent timestamp for each record from the different timestamp columns across the joined tables.
Is there a way to do a sort of max function on these fields?

Thanks!

Answer 1

I think that it is possible. For your details, consider one of approaches:

create view MyView
as
    with S1 as
    (
        select *, row_number() over (partition by main_id order by [timestamp] desc) as n from SecondaryTable1
    ),
    S2 as
    (
        select *, row_number() over (partition by main_id order by [timestamp] desc) as n from SecondaryTable2
    )
    select MainTable.id -- ... the list of output columns from three tables
    from MainTable
    inner join S1 on S1.main_id = MainTable.id
    inner join S2 on S2.main_id = MainTable.id
    where S1.n = 1 and S2.n = 1

It shows one main table and two secondary joined tables, that have foreign keys (main_id) and timestamps.

Answer 2

I think I interpret your question differently from Viorel.

From my interpretation, this is very easy to do if you are on Azure SQL Database or, I assume, Azure SQL Managed Instance:

SELECT ... greatest(t1.timestamp, t2.timestamp, t3.timestamp, ...)
FROM  ...

Alas, the greatest function is not available in any on-premises version of SQL Server. In that case you can do:

CROSS APPLY (SELECT MAX(V.timestamp) AS maxtimestamp
      FROM (VALUES(t1.timestamp), (t2.timestamp), (t3.timestamp)) AS V(timestamp)
    ) AS  M

As I said, Viorel and I seems to interpret your question differently. It could help if you post CREATE TABLE + INSERT statements with sample data and the desired result given the sample. This helps to clarify what you are looking for and it makes it easy to copy and paste into a query window to develop a tested solution. It also helps if you include the output of "SELECT @@version", so we know your presumptions.

Answer 3

Hi @Andreas Schwibach ,

Check out this similar thread,
such as, select columns that are in the group or used in an aggregate function and use a join to get this working

select s1.*   
from sensorTable s1  
inner join   
(  
  SELECT sensorID, max(timestamp) as mts  
  FROM sensorTable   
  GROUP BY sensorID   
) s2 on s2.sensorID = s1.sensorID and s1.timestamp = s2.mts  

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.