One to many relation getting recent records plus one column from previous record Table 1

ProductID   Status
PD_1       OPN
PD_2       CLS


Table2

Table2_id   ProductID   Amount 
1           PD_1        900
2           PD_1        800
3           PD_2        1000
4           PD_2        900
5           PD_2        700


Final expected result

ProductID   Recent_Amount   Previous_Amount   Status 
PD_1        900             800               OPN
PD_2        1000            900               CLS
You can get the previous row value for a column using the LAG function and limit to the most recent row using ROW_NUMBER:

SELECT ProductId,
  RecentAmount,
  PreviousAmount,
  Status
FROM
(
SELECT
  t1.ProductId,
  t2.Amount AS RecentAmount,
  LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,
  t1.Status
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.ProductId = t2.ProductId
) t
WHERE Rn = 1


Your example data is a little confusing though. For PD_2, your expected results indicate that 1000 is the most recent amount and 900 the previous amount, however, the 1000 record has a lower Id value the 900 record. Typically, one would assume the higher Id value records were entered alter than the lower ones.

In any case, you can see a working example of the LAG function, combined with ROW_NUMBER to pick the single row here. This example uses specific ordering in the LAG (DESC) and ROW_NUMBER (ASC) functions to return your expected resultset.

If your example data is incorrect and the 1000 record should actually be a higher Id value, you can change the LAG and ROW_NUMBER functions from

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS Rn,


to

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,


as shown in this example.