I want to write a query, where I take out data where INPUT_DATE is the maximum, given that I have at least 2 rows on that INPUT_DATE. So if I would have the following counts on historical days:

It should give me the data from 2022-10-24. The max INPUT_DATE where I have more than 1 row.

I have the following, but it is not working as it is either giving a table with >1 row, or no table at all, depending on whether the max INPUT_DATE include more than 1 row or not.

select * from (
select VALUE_A, VALUE_B, count(*) over (partition by INPUT_DATE) as cnt
from [db]
where VALUE_C = 'something'
and INPUT_DATE = (select max(INPUT_DATE) from 
    [db] where 
    VALUE_C = 'something' and 
    INPUT_DATE <= '2022-10-25')
    ) t
where t.cnt > 1

Desired result:

CodePudding user response：

There might be shorter options and other ways (like row number or partition etc.). But you can get the desired outcome just using GROUP BY, COUNT and MAX. This has the advantage the query will do on every DB type while many functions like row number etc. differ depending on the DB type.

SELECT input_date, value_a, value_b 
FROM yourtable
WHERE input_date =
(SELECT MAX(sub.input_date) FROM
(SELECT input_date 
FROM yourtable
GROUP BY input_date
HAVING COUNT(input_date) > 1) sub);

You can verify this will produce the correct result here: db<>fiddle