Hobo Cookbook

View Source

Question: Question How do you do a 'recently active' query in SQL?

Markdown Asks Tom
e.g. say `User has_many :recipes`, how would you write a query that ordered the users who most recently created a recipe first?

Here's what I tried and it don't be working!

SELECT * FROM "users"
ORDER BY (select created_at from recipes where recipes.user_id = users.id order by created_at limit 1)
LIMIT 6

(Yes, my SQL really is that bad)

Discussion

  • In answer to How do you do a 'recently active' query in SQL?
    jevan Says:

    This SQL should do it:

    SELECT users.* FROM users WHERE users.id IN (SELECT u1.id
FROM users u1 INNER JOIN recipes ON u1.id = recipes.user_id
GROUP BY u1.id ORDER BY Max(recipes.created_at) DESC LIMIT 6)
  • In answer to How do you do a 'recently active' query in SQL?
    jevan Says:

    The IN SQL operator won’t guarantee the order of the matched records. A query that guarantees the ordering is:

     SELECT users.*
 FROM users 
   INNER JOIN
       (  SELECT u1.id, Max(recipes.created_at) As max_created_at
          FROM users u1 INNER JOIN recipes ON u1.id = recipes.user_id
          GROUP BY u1.id
       ) As x
    ON users.id = x.id ORDER BY max_created_at DESC LIMIT 6