MySql query limiting and ordering on join statament

Question

I have a two tables as authors and articles. I want to get list of latest articles for each author. I want only one article for one author. And I want it to be the latest. But, I couldn't even figure out where to start to this sql query.

Edit

My table structure can be simplefied like this:

authors:
 id
 name
 status
 seo   
articles:
  author_id
  title
  text
  date
  seo

Edit 2

I came up with something like this, is there any obvious mistakes you can see in here:

SELECT authors.*, 
(SELECT articles.title FROM articles WHERE author_id = authors.id ORDER BY articles.date DESC LIMIT 1) as title,
(SELECT articles.seo FROM articles WHERE author_id = authors.id ORDER BY articles.date DESC LIMIT 1) as articleseo 
FROM authors 
WHERE authors.status = 1

Would you mind telling your database structure? tables?? – Ghazanfar Mir Nov 01 '11 at 14:07 — Ghazanfar Mir, Nov 01 '11 at 14:07
btw, foreign keys won't help you here – Mikhail Nov 01 '11 at 14:21 — Mikhail, Nov 01 '11 at 14:21

Mikhail · Answer 1 · 2011-11-01T17:48:27.763

1

No clue what your table structure is, but if it's what I envision then do this:

SELECT author.name, article.title FROM
    author LEFT JOIN article ON author.id = article.author_id
    GROUP BY author.id
    ORDER BY author.id, article.date DESC

edited Nov 01 '11 at 17:48

answered Nov 01 '11 at 14:19

Mikhail

8,692
8
56
82

This doesn't return most recent article, but instead, it gets first article for each author, and than order the results according to the returned articles. – yasar Nov 01 '11 at 15:33
MySQL performs a `GROUP BY` before it executes `ORDER BY`. Seems that without a nested query, or a temporary table it's hard to accomplish – Mikhail Nov 02 '11 at 21:43

score 1 · Accepted Answer · answered Nov 01 '11 at 21:10

1

Alright, I found out what I needed to do:

CREATE TEMPORARY TABLE articles2
SELECT max(date) as maxdate, author_id
FROM articles
GROUP BY author_id;

SELECT authors.name, authors.seo, articles.seo, articles.title FROM articles JOIN articles2 ON (articles2.author_id = articles.author_id AND articles2.maxdate = articles.date) JOIN authors on authors.id = articles.author_id WHERE authors.status = 1

I hope this helps someone.

answered Nov 01 '11 at 21:10

yasar

13,158
28
95
160

You can do it without the temporary table if you use a subquery. – Joshmaker Oct 18 '12 at 21:06

score 0 · Answer 3 · answered Nov 01 '11 at 14:19

0

I'll do something like that, but if you post the database structure I'll be more specific

    SELECT * 
    FROM articles,authors 
    WHERE articles.aut = authors.aut
    GROUP BY authors.aut 
    ORDER BY articles.date DESC

answered Nov 01 '11 at 14:19

Marcx

6,806
5
46
69

1

Arguably bad form to use implicit join -- see second answer in http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Matt Fenwick Nov 01 '11 at 14:24

MySql query limiting and ordering on join statament

Edit

Edit 2

3 Answers3

Linked