How do I query sql for a latest version of a record in the same date

I am trying to perform a query that returns the value of the last published version. The versions can be named V1, V2, R1_V1, R1_V2, R2_V1, R2_V2 and are published in this order. The first post will always be #null. Not all versions are dependent, I mean, the first version of the day can be registered as R1_V1 without first publishing V1 and V2.

Versions contain hourly logs, that’s why I require to always take the latest version.

Here is a brief example:

Answer

Next time, paste your input data as text into your question. I had to manually re-type everything ….

You need an additional sorting value, in addition to the version; then, you need to add a filter value based on the sorting order. Use an OLAP function.

WITH
-- your input ... not part of the final query
indata(dt,ver,val) AS (
            SELECT DATE '2020-02-01',NULL   ,1.1
  UNION ALL SELECT DATE '2020-02-01','V1'   ,1
  UNION ALL SELECT DATE '2020-02-01','R1_V1',1.5
  UNION ALL SELECT DATE '2020-02-02',NULL   ,1.2
  UNION ALL SELECT DATE '2020-02-03',NULL   ,1.3
  UNION ALL SELECT DATE '2020-02-03','V1'   ,1.5
  UNION ALL SELECT DATE '2020-02-03','R1_V1',1.2
  UNION ALL SELECT DATE '2020-02-03','R1_V2',1.3
)
-- end of your input, query starts below, replace ',' with 'WITH' ...
,
-- add a sorting integer. 0 if "ver" is NULL, 1 if it's 'V1', 2 otherwise
w_sort AS (
  SELECT
    *
  , CASE 
      WHEN ver IS NULL THEN 0
      WHEN ver = 'V1'  THEN 1
      ELSE                  2
    END AS srt
  FROM indata
)
,
-- add a filtering integer that is at 1 with the max order value
-- on the sorting criteria, so sort descending
w_fltr AS (
  SELECT
    *
  , ROW_NUMBER() OVER(PARTITION BY dt ORDER BY srt DESC, ver DESC) AS fl
  FROM w_sort
)
-- finally filter by the filter value created above, at 1
SELECT 
  dt
, ver
, val
FROM w_fltr
WHERE fl=1;
-- out      dt     |  ver  | val 
-- out ------------+-------+-----
-- out  2020-02-01 | R1_V1 | 1.5
-- out  2020-02-02 |       | 1.2
-- out  2020-02-03 | R1_V2 | 1.3