Fetch last item in a category that fits specific criteria

Question

Let's assume I have a database with two tables: categories and articles. Every article belongs to a category.

Now, let's assume I want to fetch the latest article of each category that fits a specific criteria (read: the article does). If it weren't for that extra criteria, I could just add a column called last_article_id or something similar to the categories table - even though that wouldn't be properly normalized.

How can I do this though? I assume there's something using GROUP BY and HAVING?

Answer 1

If you provide us with the Tables schemas, we could be a little more specific, but you could try something like (12.2.9.6. EXISTS and NOT EXISTS, SELECT Syntax for LIMIT)

SELECT  *
FROM    articles a
WHERE   EXISTS  (   
                    SELECT  1 
                    FROM    articles 
                    where   category_id = a.category_id
                    AND     <YourCriteria Here>
                    ORDER BY    <Order Required : ID DESC, LastDate DESC or something?
                    LIMIT 1
                )

Answer 2

Assuming the id's in the articles table represent always increasing numbers, this should work. Using the id is not semantically correct IMHO, you should actually use a time/date tamp field if one is available.

SELECT * FROM articles WHERE article_id IN
  (
    SELECT 
      MAX(article_id) 
    FROM
      articles 
    WHERE [your filters here]
    GROUP BY 
      category_id
  )

Answer 3

Try with:

   SELECT * 
     FROM categories AS c
LEFT JOIN (SELECT * FROM articles ORDER BY id DESC) AS a
       ON c.id = a.id_category
      AND /criterias about joining/
    WHERE /more criterias/
 GROUP BY c.id