SQL nested query

Question

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 most 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

@standup75 I've edited your question to clarify it, according to your comments, but I don't have time to rewrite the answer now. Good luck. — Nikita Rybak, Feb 17 '11 at 17:51
Is there a column in table images that indicates the date/time the image was added/created? Otherwise, how does one determine how recent an image is? — rskar, Feb 17 '11 at 18:00
Frequently answered question. E.g., http://stackoverflow.com/questions/1140064/sql-query-to-get-most-recent-row-for-each-instance-of-a-given-key — pilcrow, Feb 17 '11 at 18:17
For performance sake I'd suggest using EXISTS instead of IN. — Darth Continent, Feb 17 '11 at 18:34
@Darth Continent `EXISTS` would not work here. He is getting a set of ID's — Matthew, Feb 17 '11 at 19:53

score 0 · Accepted Answer · answered Feb 17 '11 at 18:36

0

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)

answered Feb 17 '11 at 18:36

rskar

4,607
25
21

Using ActiveRecord I found: Image.where(:location_id => Location.where("latitude > ? AND latitude < ? AND longitude > ? AND longitude < ?", 17.954, 52.574, -107.392, -64.853)).where(:created_at => Image.group(:location_id).maximum(:created_at).values) – standup75 Feb 17 '11 at 21:10

user617850 · Answer 2 · 2011-02-17T18:49:15.050

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
        )

SQL nested query

2 Answers2