Order by over two tables?

[gausie]

Hi all

 

I have one table with a list of articles, and another with comments having an "article_id" to relate to which article they are about.

 

How can I take the list of articles in order of how many comments there are? i.e. one with most comments comes out first etc.

 

gausie

[Wildbug]

Remember, it doesn't matter that you're gathering data from two tables.  You can ORDER BY any column in the result row.  To find the number of comments, you'll need to use GROUP BY to group the articles and count the comments relating to them.  Unfortunately, if you want the comments as well, the grouping will collapse them, so you'll need to do this with a subquery (4.1+) or two separate queries.

 

# To simply find articles in order of comments:
SELECT articles.article_id,title,COUNT(comment) AS num_comments
FROM articles LEFT JOIN comments ON articles.article_id=comments.article_id
GROUP BY articles.article_id
ORDER BY num_comments;

# To select the comments as well:
SELECT articles.article_id,title,comment.timestamp, comment
FROM articles LEFT JOIN comments ON articles.article_id=comments.article_id
LEFT JOIN (SELECT article_id,COUNT(comment) AS count FROM comments GROUP BY count) AS cc ON articles.article_id=cc.article_id
ORDER BY cc.count, comments.timestamp;

[gausie]

Thanks so much for that help!

 

COUNT(comment) - what is this "comment" referring to? If my comments table is called "comments", should that be "COUNT(comments)"?

 

gausie

[Wildbug]

Since I didn't know your table structure, I made up the column and table names.

 

The COUNT(comment) refers to the actual, stored comment value within the comment table, not the comment table itself.

[gausie]

What do you mean by "comment value"?

 

gausie

[Wildbug]

Why don't you post your table structures, and I can give you a more precise answer.

[gausie]

That's "mak" (the articles table) ->

 

FieldTypeCollationNullExtra

idint(250)[/td]Noauto_increment

namevarchar(250)latin1_swedish_ciNo

infolongtextlatin1_swedish_ciNo

typevarchar(250)latin1_swedish_ciNo

uservarchar(250)latin1_swedish_ciNo

ratingint(250)No

votesint(250)No

dateint(250)No

 

 

That's "comments" ->

 

FieldTypeCollationNullExtra

idint(250)Noauto_increment

commentlongtextlatin1_swedish_ciNo

mak_idint(250)No

uservarchar(250)latin1_swedish_ciNo

dateint(11)No[td]

 

Hope that's enough information!

(and date means timestamp )

 

gausie

[Wildbug]

So, using your table and column names, the first query I posted becomes:

 

SELECT mak.id,mak.name,COUNT(comments.comment) AS num_comments
FROM mak LEFT JOIN comments ON mak.id=comments.mak_id
GROUP BY mak.id
ORDER BY num_comments;

 

And the second...

 

SELECT mak.id,title,comments.date, comments.comment
FROM mak LEFT JOIN comments ON mak.id=comments.mak_id
LEFT JOIN (SELECT mak_id,COUNT(comment) AS count FROM comments GROUP BY count) AS cc ON mak.id=cc.mak_id
ORDER BY cc.count, comments.date;

[gausie]

#1056 - Can't group on 'count'

 

Thanks for your help so far, by the way, you've been excellent!

[Wildbug]

Typo; should have been mak_id.

 

SELECT mak.id,title,comments.date, comments.comment
FROM mak LEFT JOIN comments ON mak.id=comments.mak_id
LEFT JOIN (SELECT mak_id,COUNT(comment) AS num_comments FROM comments GROUP BY mak_id) AS cc ON mak.id=cc.mak_id
ORDER BY cc.num_comments, comments.date;

[gausie]

That works - excellent!

 

One last question:

 

SELECT mak.id,name,mak.user,comments.date,comments.user,num_comments

FROM mak LEFT JOIN comments ON mak.id=comments.mak_id

LEFT JOIN (SELECT mak_id,COUNT(comment) AS num_comments FROM comments GROUP BY about)AS cc ON mak.id=cc.mak_id

ORDER BY comments.date;

 

How can I make it only return one (the most recent) comment?

 

gausie

 

 

[Wildbug]

SELECT comment FROM comments ORDER BY date LIMIT 1;

 

(You can add more columns in the SELECT... part.)

 

You can return the article as well using a JOIN.

 

SELECT name,comment,comments.date
FROM mak JOIN comments ON mak.id=comments.mak_id
ORDER BY comments.date
LIMIT 1;

[gausie]

I tried to do this myself but no luck -

 

I want to select each article, ordered by the most recent comment, and I want to return information about that most recent comment for each article. How do I do that?

 

gausie

[Wildbug]

SELECT mak.id, mak.name, comments.date, comments.comment FROM mak JOIN comments ON mak.id=comments.mak_id JOIN (SELECT mak_id, MAX(id) AS latest FROM comments GROUP BY mak_id ORDER BY latest DESC) AS c2 ON comments.id=c2.latest ORDER BY comments.date DESC

 

That should work to get you the article and latest comment sorted by most recent as long as the assumption holds that the higher comment.id directly corresponds to a more recent comment.  You can add a LIMIT in the subquery if you want.  I also assumed you wouldn't want repeated articles.

[gausie]

And finally, could it give a count of the comments as well?

[Wildbug]

Add a COUNT(*) in the subquery.

[gausie]

Thankyou for your amazing help! Topic solved

[gausie]

Wait no :-(

 

I tried to add a WHERE clause to picking from the mak table. I want sometimes to select only the articles with the "type" column having then value "item".

 

I tried adding a WHERE mak.type = "item", but I got an error in MySQL syntax

 

SELECT mak.id, mak.name, mak.user AS poster, mak.type, mak.rating, mak.votes, comments.date, c2.amount, comments.user

FROM mak

JOIN comments ON mak.id = comments.about

JOIN (

 

SELECT about, MAX( id ) AS latest, COUNT( * ) AS amount

FROM comments

GROUP BY about

ORDER BY latest DESC

) AS c2 ON comments.id = c2.latest

GROUP BY mak.id

WHERE mak.type = 'item'

ORDER BY comments.date DESC

 

Laasst piece of help?

[Wildbug]

You put it in the wrong place.  GROUP BY, ORDER BY, and HAVING clauses come after WHERE conditions.  See the SELECT syntax in the MySQL manual for details.

[gausie]

SELECT mak.id, mak.name, mak.type, mak.user AS poster, mak.rating, mak.votes, comments.date, c2.amount, comments.user FROM mak

JOIN comments ON mak.id = comments.about

 

JOIN (

SELECT about, MAX( id ) AS latest, COUNT(*) AS amount

FROM comments

WHERE comment NOT LIKE '<i>%</i>%'

GROUP BY about

ORDER BY latest DESC )

 

AS c2 ON comments.id = c2.latest

GROUP BY mak.id

ORDER BY comments.date DESC;

 

How can I make it discout the "WHERE comment NOT LIKE '<i>%</i>%' " if the amount of comments for this article is equal to 1?

 

gausie

[Illusion]

SELECT mak.id, mak.name, mak.type, mak.user AS poster, mak.rating, mak.votes, comments.date, c2.amount, comments.user FROM mak
JOIN comments ON mak.id = comments.about

JOIN (
SELECT about, MAX( id ) AS latest, COUNT(*) AS amount
FROM comments
WHERE comment NOT LIKE '\%%'
GROUP BY about HAVING amount>1
ORDER BY latest DESC )

AS c2 ON comments.id = c2.latest
GROUP BY mak.id
ORDER BY comments.date DESC;

if we don't use ' \' it won't return any records

[Wildbug]

Are you using ...comment NOT LIKE '%%'... to screen empty comments?  If so, don't use the LIKE comparison, just use the empty string and "not equal" operator, e.g., ....comment != ''.... -- it's twice as fast.

[Illusion]

when comment is a word we can use !=  if comment is a text , will it works?