Geek Answers Handbook

Select individual records based on one field while maintaining other fields

I have a table like this:

table: searches +------------------------------+ | id | address | date | +------------------------------+ | 1 | 123 foo st | 03/01/13 | | 2 | 123 foo st | 03/02/13 | | 3 | 456 foo st | 03/02/13 | | 4 | 567 foo st | 03/01/13 | | 5 | 456 foo st | 03/01/13 | | 6 | 567 foo st | 03/01/13 | +------------------------------+

And I want the result set to look like this:

 +------------------------------+ | id | address | date | +------------------------------+ | 2 | 123 foo st | 03/02/13 | | 3 | 456 foo st | 03/02/13 | | 4 | 567 foo st | 03/01/13 | +------------------------------+

But ActiveRecord does not seem to be able to achieve this result. Here is what I am trying:

  • The model has the most_recent scope :most_recent, order('date_searched DESC') : scope :most_recent, order('date_searched DESC')
  • Model.most_recent.uniq returns the full set ( SELECT DISTINCT "searches".* FROM "searches" ORDER BY date DESC ) - it is obvious that the query will not do what I want, but none of them selects only one column. I need all the columns, but only the rows where address is unique in the result set.
  • I could do something like Model.select('distinct(address), date, id') , but that ... is wrong.
+4
source share
2 answers

You can do

 select max(id), address, max(date) as latest from searches group by address order by latest desc

According to sqlfiddle , which does exactly what I think you want.

This is not exactly the same as the output of your request, which does not seem to care about which identifier is returned. However, the request must specify something that is done here using the aggregate function "max".

I don't think you're lucky with the auto-generated ActiveRecord query methods for this case. So just add your own query method using this SQL for your model class. This is completely standard SQL, which will also work mainly with any other DBMS.

Edit:. The big weakness of the query is that it does not necessarily return the actual records. If the highest identifier for a given address does not match the highest date for this address, the resulting "record" will be different from the one actually stored in the database. Depending on the use case, which may or may not matter. For Mysql, just changing max(id) to id will fix this problem, but Oracle IIRC has a problem with this.

+3
source

To show unique addresses:

 Searches.group(:address)

Then you can select the columns if you want:

 Searches.group(:address).select('id,date')
+2
source

More articles:


All Articles