I have a table like the below:

I want to make a query to receive the first record that "last update" = null and 2 records before it. The result should be:

CodePudding user response：

Use this:

select * from yourTable where lastUpdate=(select Max(lastUpdate) from yourTable)
Union all 
select id,name,date,lastUpdate from 
    (select *,ROW_NUMBER() over (partition by lastUpdate order by id)as rn from yourTable where lastUpdate is null )a where rn=1

Output:

id  name    date    lastUpdate
2   test2   2021-02-01  2021-06-01
3   test3   2021-03-01  2021-06-01
4   test4   2021-04-01  NULL

CodePudding user response：

A simpler solution using "with" statement as described here.

WITH dat AS 
  (SELECT ID FROM records WHERE last_update ISNULL ORDER BY ID LIMIT 1)   --Finding the first null

SELECT r.* 
FROM
    records r, dat 
WHERE
    r.ID BETWEEN ( dat.ID - 2 ) AND dat.ID                              --Finding 2 records before
ORDER BY r.ID;

CodePudding user response：

You can look for the date that has the first NULL last_update.
Then get also the 2 before that date.

SELECT *
FROM
(
    SELECT * 
    FROM your_table
    WHERE date <= (
      SELECT date  -- the date of the first NULL last_update
      FROM your_table 
      WHERE last_update IS NULL 
      ORDER BY date ASC NULLS LAST
      LIMIT 1
    )
    ORDER BY date DESC
    LIMIT 1 2      -- the NULL last_update   2 records before it
) q
ORDER BY date ASC;

Demo on db<>fiddle here