SQL nested query

I have an images table and a locations table I want to retrieve a list of images that are the latest images for each location within a certain boundaries.

SELECT * FROM images 
    WHERE location_id IN 
       (SELECT id FROM locations 
            WHERE latitude > 17.954 AND latitude < 52.574 
            AND longitude > -107.392 AND longitude < -64.853)

This is with a nested query, but we could achieve the same with a join. This works if we want all images for each location, but I would like to get only 1 image per location (the m开发者_如何学Goost recent)

Here are the main fields of these tables

  table "images"
    integer  "id"
    text     "image_name"
    text     "caption"
    integer  "location_id"
    datetime "created_at"
    datetime "updated_at"
    integer  "view_count"

  table "locations"
    integer  "id"
    text     "name"
    float    "longitude"
    float    "latitude"
    datetime "created_at"
    datetime "updated_at"
    string   "city"
    string   "address"
    string   "province"
    string   "country"
    string   "post_code"

any idea?

Bonus points if there is a way to do this using rails activerecord API

You will need to make use of aliases and aggregation in a sub-query.

SELECT * FROM images IMG
    WHERE location_id IN
       (SELECT id FROM locations
           WHERE latitude > 17.954 AND latitude < 52.574
           AND longitude > -107.392 AND longitude < -64.853)
    AND created_at IN
       (SELECT MAX(created_at) FROM images IMG2
              WHERE IMG2.location_id=IMG.location_id)

SELECT *
FROM Images I     
INNER JOIN Location L On L.Id = I.Location_ID
WHERE (L.latitude Between 17.954 And 52.574)              
    AND (L.longitude Between -107.392 And -64.853)

You Should use Top 1 Ordering by your date desc, incase you have 2 dates in the same location with the Same Updated Date

SELECT *
FROM Images I     
INNER JOIN Location L On L.Id = I.Location_ID
WHERE (L.latitude Between 17.954 And 52.574)              
    AND (L.longitude Between -107.392 And -64.853)
    AND I.Id = 
        (
            SELECT Top 1 I2.Id 
            FROM Images I2 
            WHERE I2.Location_Id = L.Id 
            ORDER BY Updated_At DESC
        )