postgresql

Select multiple rows using "IN", selecting closest less than element if not in

Suppose I have the following table:

Value1 Value2
1 a
2 b
3 c
5 e
6 f

I want to "align" it to a vector, e.g. (2, 4, 6), however, I want it to "frontfill" to include data from the first row where Value1 is less than any value which is not included in the original table. This would produce the output:

Value1 Value2
2 b
4 c
6 f

Simply using "WHERE IN (2,4,6)", I would only get the table below, without the desired "frontfill" behaviour.

Value1 Value2
2 b
6 f

Supposing that I only wanted to match a single row I would simply use less than equal and LIMIT 1, however, I'm not sure how to apply this for multiple values without looping (which feels like it'd be slow).

The specific use case for which I'm looking for this solution is financial data that I want to align to a vector of dates.

Solution

  • I would use DISTINCT ON to do this. For example:

    with cte as
(
    Values(2),(4),(6)
)
SELECT DISTINCT ON (c.column1) c.column1, y.value2 
FROM cte c
inner join your_table y
ON y.value1 <= c.column1
ORDER BY c.column1, y.value1 DESC;

    The key thing to note when using DISTINCT ON is that the first column in the order by list needs to be the column in the distinct. It is therefore the second column in the ORDER BY which determines the value returned. Note also that the second column in the ORDER BY does not need to be in the SELECT list.