SQL query to get fields from 2nd table based on input from 1st-CodePudding

I have 2 tables, table1 and table2

table1 -

sn	timestamp
123	123456

table2 -

sn	timestamp	code
456	123456	xxxxx

I want to first get the sn from table1 with max timestamp(epoch) and then query table 2 with the sn returned from table1 and get the code from table2 with the max timestamp.

I am trying this but getting errors -

select code from table2 where sn = (select sn, max(timestamp) from table1 GROUP BY sn)

Should i use joins instead?

CodePudding user response：

To get the "latest" value I always use windowing functions.

In order to keep everything clean, I've used two CTEs to define the two datasets but you can put everything in a single query if needed.

WITH latestDataT1 AS (
SELECT 
     sn
    ,timestamp
FROM (
    SELECT
        sn
        ,timestamp
        ,ROW_NUMBER() OVER(ORDER BY timestamp DESC) as RowNo
    FROM table1
)
WHERE
    RowNo = 1
)
WITH latestDataT2 AS (
SELECT
     sn
    ,timestamp
    ,code
FROM (
    SELECT 
         sn
        ,timestamp
        ,code
        ,ROW_NUMBER() OVER(PARTITION sn BY ORDER BY timestamp DESC) as RowNo
    FROM table2
)
WHERE
    RowNo = 1
)

SELECT
    *
FROM latestDataT1 AS t1
LEFT JOIN latestDataT2 AS t2
    ON t1.sn = t2.sn

CodePudding user response：

Can you try one of these scripts:

1) select code from table2 where (sn, timestamp) in (select sn, max(timestamp) from table1 GROUP BY sn);

select A.code, B.maxtime 
  from table2 A,  
       (select sn, max(timestamp) as maxtime 
          from table1 GROUP BY sn) B
 where A.sn        = B.sn
   and A.timestamp = B.maxtime;

Thank you

CodePudding user response：

Try this:

select DISTINCT code from table2 
where sn = (select sn from table1 WHERE timestamp = (SELECT MAX(timestamp) FROM Table1));

CodePudding user response：

You can do this using the following code:

SELECT code
from table2 t
where sn = (SELECT sn
            FROM table1 t1
            INNER JOIN (SELECT MAX(timestamp) AS max_ts
                        FROM table1) d
              ON t1.timestamp = d.max_ts
           )
INNER JOIN (SELECT MAX(timestamp) AS max_ts
           FROM table2) t2
  ON t.timestamp= t2.max_ts;

However using the example tables you provided this will return no results, as the sn 123 isn't found in table2