CODEWITHSUNDEEP
ruby-on-railspostgresqlrails-activerecord
Oliver
Oliver

Reputation: 1181

Rails: select records with maximum date

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.

Upvotes: 9

Views: 13830

Answers (2)

aidan
aidan

Reputation: 9576

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.

Upvotes: 19

Shannon
Shannon

Reputation: 3018

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

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

Upvotes: 5

Related Questions