JOIN with ORDER or MAX()?

Question

Example. I need to get list of posts and match 1 comment to each one (newest, oldest or most rated).

Are there any suggestions?

SELECT *
FROM posts p
LEFT JOIN comments c
ON p.id = c.post_id
GROUP BY p.id

And now I need to set, which one from comments (top, newest or oldest) must be matched with post. But I really don't know how to.

Can you give query then we can help more which you have tried. — Sadikhasan, Jun 24 '14 at 09:48
You're using MySQL so you probably need to use a correlated subquery. SQL Server you could use `(CROSS|OUTER) APPLY` with `SELECT TOP 1 ... ORDER BY whatever` — ta.speot.is, Jun 24 '14 at 09:56
ta.speot.is, I think it must be some way not to use subquery.. — Taras Kudrych, Jun 24 '14 at 09:58
This question appears to be off-topic because it lacks information to diagnose the problem. Consider providing proper DDLs (and/or an sqlfiddle) TOGETHER WITH THE DESIRED RESULT SET. — Strawberry, Jun 24 '14 at 10:02

score 0 · Answer 1 · answered Jun 24 '14 at 10:26

0

Newest:

SELECT MAX(с.created) ........
FROM comments c
GROUP BY c.post_id

Oldest:

SELECT MIN(с.created) ........
FROM comments c
GROUP BY c.post_id

Most rated:

SELECT MAX(с.rating) ........
FROM comments c
GROUP BY c.post_id

answered Jun 24 '14 at 10:26

Taras Kudrych

1 Answers1