CODEWITHSUNDEEP
mysqldatabase
user1297746
user1297746

Reputation: 27

Get the entire row of the maximum value of a column in every category

I have a table containing news posts and would like to get the entire last added newspost of every category.
The table contains these columns:

Example: I have 20 posts in 3 categories. The last post by date of every category is returned so I get 3 posts back.

Upvotes: 0

Views: 84

Answers (2)

TheHe
TheHe

Reputation: 2972

think the smallest and most usable solution for this is that one:

SELECT * FROM ( 
   SELECT * FROM table ORDER BY date DESC
) as t
GROUP BY t.category;

an other approach will be the group having with subquery...

@eggyal: This will use the LASTEST inserted with max_date, because of the internal sortorder of the rdbms...

Upvotes: 0

eggyal
eggyal

Reputation: 125835

You want the groupwise maximum:

SELECT newsposts.* FROM newsposts NATURAL JOIN (
  SELECT category, MAX(date) AS date FROM newsposts GROUP BY category
) t

Upvotes: 1

Related Questions