CODEWITHSUNDEEP
mysql
Boris
Boris

Reputation: 749

MySQL query to get last record in each category

I got two tables: articles and categories (about 20 categories), and I want to get the lastest 10 articles, but no more than one from a category.

Table Articles

id | title | text

Table Categories

id | name

Table ArticlesCategories (relationsl table between Articles and Categories)

article | category

Im using the query below but the problem is that the 10 results are not all the latest articles.

SELECT id, title, categoryId, categoryName
FROM ( 
        SELECT a.id, a.title, ac.category AS categoryId, c.name AS categoryName
        FROM articles AS a 
            LEFT JOIN articles_categories AS ac ON ac.article = a.id 
            LEFT JOIN categories AS c ON c.id = ac.category 
        WHERE ac.priority = 1 
        ORDER BY a.id DESC ) AS tmp_table 
GROUP BY categoryId LIMIT 10

Upvotes: 0

Views: 1615

Answers (1)

underscore
underscore

Reputation: 6877

Add ORDER BY id DESC to your query

This is the way of getting the last 10 rows is to reverse the order and select the first ten rows:

SELECT id, title, categoryId, categoryName
FROM ( 
        SELECT a.id, a.title, ac.category AS categoryId, c.name AS categoryName
        FROM articles AS a 
            LEFT JOIN articles_categories AS ac ON ac.article = a.id 
            LEFT JOIN categories AS c ON c.id = ac.category 
        WHERE ac.priority = 1 
        ORDER BY a.id DESC ) AS tmp_table 
GROUP BY categoryId  ORDER BY id DESC LIMIT 10

Upvotes: 2

Related Questions