Reputation: 3562
this is my table , name is resource_calendar.
i want to select resource_id which have effective date less then or equal to current date and most closest date to current date.
what will be the right query in postgresql?
query will
select effective date 22 for resource_id=3 and effective date 21 for resource_id=7
so result should be
id resource_id calendar_id applied_on effective_date version
19 3 6 2016-12-22 11:13:26.53 2016-12-22 0
26 7 5 2016-12-22 11:16:26.53 2016-12-21 0
Upvotes: 1
Views: 2853
Reputation: 11983
You can use this. A simple query
SELECT DISTINCT ON(resource_id) *
FROM planner.resource_calendar
WHERE effective_date <= CURRENT_DATE
ORDER BY resource_id, effective_date desc;
Upvotes: 2
Reputation: 522817
SELECT t.*
FROM
(
SELECT id, resource_id, calendar_id, applied_on, effective_date, version,
MIN(ABS(EXTRACT(EPOCH FROM (current_timestamp - effective_date))))
OVER (PARTITION BY resource_id) AS diff
FROM resource_calendar
WHERE EXTRACT(EPOCH FROM (current_timestamp - effective_date)) > 0
) t
WHERE ABS(EXTRACT(EPOCH FROM (current_timestamp - t.effective_date))) = t.diff
This query forms a partition by resource_id
on the resource_calendar
. You can think of this partition as a logically grouping records together which have the same resource_id
. For each such group of records, it computes the smallest difference between the effective_date
and the current timestamp, where the effective_date
be earlier than the current timestamp.
The outer query then identifies those records having this minimum timestamp difference.
Postgres has some reasonably helpful documentation on using window functions if you feel you need more information.
Upvotes: 2