Mysql query GROUP BY where condition LIMIT 1

Question

I have a table called "articles" on the database

articles :

id +++   writer_id    +++     title     ++++    text
----------------------------------------------------
1           1              some title        article for writer 1 
2           1              some title        article for writer 1 
3           2              some title        article for writer 2 
4           2              some title        article for writer 2 
5           2              some title        article for writer 2
6           3              some title        article for writer 3

I need a query to get the latest articles from the table BUT just ONE article for each Writer

depends on the rows above the query should get just 3 rows : each article owns by ONE writer

2           1              some title        article for writer 1 
5           2              some title        article for writer 2
6           3              some title        article for writer 3

php

SELECT * 
FROM articles
WHERE writer_id = ???? ;
order by id desc limit 10

thanks in advance : )

score 1 · Answer 1 · answered Aug 03 '12 at 21:49

1

Using a combination of GROUP BY and HAVING will allow you to get the last article written by each author, and have them ordered properly:

SELECT * 
FROM articles
WHERE writer_id = ????
GROUP BY writer_id
HAVING id = MAX(id)
ORDER BY id DESC
LIMIT 10

answered Aug 03 '12 at 21:49

newfurniturey

37,556
9
94
102

score 1 · Accepted Answer · answered Aug 03 '12 at 21:54

1

Short and sweet:

SELECT MAX(id), writer_id, title, text FROM articles GROUP BY writer_id;

answered Aug 03 '12 at 21:54

Kalpesh

5,635
2
23
39

This won't actually correlate the correct article text and title with the correct dates (but it will appear to work with the example data given here because the titles and text are repeated). See http://stackoverflow.com/a/7972973/351048 for a better solution. – Joshmaker Oct 18 '12 at 20:18

score 0 · Answer 3 · answered Aug 03 '12 at 21:49

0

Use a subquery to get the latest article id per writer:

SELECT * FROM articles
WHERE id in
(
    SELECT MAX(id) id, writer_id
    FROM articles
    GROUP BY writer_id
)

answered Aug 03 '12 at 21:49

McGarnagle

101,349
31
229
260

Mysql query GROUP BY where condition LIMIT 1

3 Answers3