Problem with SQL

Anon

I'm trying to write a query that will do the following:

I store articles in a table, and each article has a unique id (article_id). And then if some one responds to that article (a comment!) then, the unique id (article_id) is stored in a field called parent_id.

So for example, if I want to find out how many replies have been made to an article the query is as follows.

SELECT count(*) FROM article WHERE parent_id=article_id;

And that will give me a total of how many replies there have been.

The problem is this: I'm also trying to write a top 10 most active articles list, which involves finding out which articles have the most replies (and listing them descendingly)

I have a query but it doesn't seem to work. If anyone can suggest the correct query, I'd be most greatful!

Here's what I have so far:

SELECT *, COUNT(parent_id) AS c FROM article WHERE article_id = parent_id GROUP BY article_id ORDER BY c DESC LIMIT 0, 10

Regards, James.

Anon

select parent_id, count(*) as replies
from article
group by parent_id
order by replies desc limit 0,10

Anon

Thank you for reply, but sadly it didn't work. While it did actually get some results (unlike my effort), it didn't sort them properly for some reason.

Also, I have to do 'SELECT *' in the query, because otherwise, the data won't be returned.

Thanks
Regards, James.

Anon

What do you mean by not sorted properly? If the result is not sorted by the number of replies descendingly, that inidicates an error in Mysql.

You should not use select * but rather name all columns that you are interested in

select a1.article_id,a1.article,
count(*) as replies
from article a1 inner join article a2
on a1.article_id = a2.parent_id
group by a1.article_id,a1.article
order by replies desc

All columns in the select list (excluding those that are used in an agregate function) should be present in the group by clause.

Anon

Excellent! Thank you very much 🙂 It worked a treat.

Thanks for your help Lars.

James.

Anon

Another question, if you could help again it'd be appreciated.

Say that I wanted to sort them in descending order via most replies (which has been achieved, thanks to Lar's help), but I wanted to only view articles that were of a certain topic, like 'Humour' for example...?

Here's the existing query:

SELECT , a1.article_id, COUNT() AS replies FROM article a1 INNER JOIN article a2 ON a1.article_id = a2.parent_id GROUP BY a1.article_id ORDER BY replies DESC LIMIT 0, 10

Where, if possible, would I put a 'WHERE' clause that would look for an article of a certain type? E.G. 'WHERE topic_type='Humour'' ?

Thanks in advance.

Regards,
James.

laccata

From your posting you have the TOPIC_TYPE column in the same table (ARTICLE) as the other columns that you want to extract.

In this case you do not need to join the table with itself.

All you need to do is add the WHERE clause before the GROUP BY, so

SELECT COL1, COL2, ..., COUNT(*) AS FUNNY_COUNT
FROM ARTICLE
WHERE TOPIC_TYPE = \'Humour\'
GROUP BY COL1, COL2, ...
ORDER BY FUNNY_COUNT DESC
LIMIT 0, 10

Hope this helps.