CODEWITHSUNDEEP
mysqlruby-on-rails
bpromas
bpromas

Reputation: 694

Capturing the record with the highest value for one column in a single query?

I have a table that looks something like this:

| id | fk1 | fk2 | version |
|  1 |  1  |  1  |    1    |
|  2 |  1  |  1  |    2    |
|  3 |  1  |  1  |    3    |

Having on hand the values of fk1 and fk2 I am trying to get the record with the highest value for version. Currently what I am doing is this:

version = Project.where("fk1= ? AND fk2= ?", params.require(:fk1), params.require(:fk2)).maximum(:version)
@project = Project.find_by_fk1_and_fk2_and_version(params.require(:fk1), params.require(:fk2), version)

This gets me the correct record, but I have to execute 2 queries for something that seems really simple in theory, but after trying a number of different things I had no luck with doing this with a single query. I am envisioning something like:

version = Project.where("fk1= ? AND fk2= ? AND max(version)", params.require(:fk1), params.require(:fk2))

or something.

Upvotes: 3

Views: 37

Answers (1)

xlembouras
xlembouras

Reputation: 8295

Well the rails way to do that is

Project.where(fk1: params.require(:fk1), fk2: params.require(:fk2)).
  order('version desc').first

Which translates to an sql query like:

SELECT * FROM projects WHERE fk1 = "fk1" AND fk2 = "fk2" ORDER BY version DESC LIMIT 1;

Upvotes: 1

Related Questions