PostgreSQL Question posted in
The official documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    You could use a subselect to extract the needed information.

    The idea here is to select all rows from your table, where value1 is less than or equal to the requested value (2,4,6) and then return the one with the maximum value (in my example by order by desc and limit 1)

    select value1, 
(select value2 from mytable where value1 <= mytable2.value1 order by value1 desc limit 1) value2 
from (values (2),(4),(6)) as mytable2(value1);

    https://sqlfiddle.com/postgresql/online-compiler?id=84520866-62bc-47aa-be3d-cc555b7d6da1

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search