I am trying to come up with a fast query that will select the last entry in a table for every month.

I have an entry for every day over a period of several years and I want to see the last entry for each month over that time span. I am using postgresql on the server. I can create a function that given a date will return the last day of the month but applying that function to the query makes the query run very slowly.

I you provide a part of the search-routine and function it will be easier to help

Sorry about that, I ended up having to run and didn't get to finish as well as I wanted.

Table has two fields: day is a date type, val is a double. I have data for almost every day over the last 3 years.

CREATE TABLE entries (day date primary key,val double );

CREATE FUNCTION month_close(DATE) RETURNS DATE AS '

SELECT MAX(day) FROM entries WHERE

EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM $1) AND

EXTRACT(YEAR FROM day) = EXTRACT(YEAR FROM $1);

' LANGUAGE 'sql';

-- Given some entries in the table

SELECT * FROM entries WHERE day = month_close(day);

-- Wait for 2 minutes or so and the query finally returns data


I could probably come up with a select that gives me all the last day in the month over a span of months and the use that for an IN but there are occasions where there was no data collected on the last day of the month. For those instances I would like to grab the data from the last entry for the month.