Rails: select records with maximum date

Question

In my app users can save sales reports for given dates. What I want to do now is to query the database and select only the latest sales reports (all those reports that have the maximum date in my table).

I know how to sort all reports by date and to select the one with the highest date - however I don't know how to retrieve multiple reports with the highest date.

How can I achieve that? I'm using Postgres.

Answer 1

You can get the maximum date to search for matching reports:

max_date = Report.maximum('date')
reports = Report.where(date: max_date)

Answer 2

Something like this?

SalesReport.where(date: SalesReport.maximum('date'))

EDIT: Just to bring visibility to @muistooshort's comment below, you can reduce the two queries to a single query (with a subselect), using the following form:

SalesReport.where(date: SalesReport.select('MAX(date)'))

If there is a lot of latency between your web host and your database host, this could halve execution times. It is almost always the preferred form.