CODEWITHSUNDEEP
mysql
Victor Bjelkholm
Victor Bjelkholm

Reputation: 2016

Order threads by the number of posts

I got a table with threads and one with posts. I want to list these ordered by the number of post associated to them.

Example structure of my tables

Posts table:

id   creator    replyTo    text    timestamp
1    1          1          Bla     2011-11-11 11:11
2    2          2          Alb     2011-11-11 11:11
3    3          3          Lba     2011-11-11 11:11
4    4          1          Lab     2011-11-11 11:11
5    5          2          Bal     2011-11-11 11:11
6    2          2          Abl     2011-11-11 11:11

Threads table:

id  creator     name       content    timestamp
1   1           BlaBla     BlaBla     2011-11-11 11:11
2   3           AlbAlb     AlbAlb     2011-11-11 11:11
3   2           LbaLba     LbaLab     2011-11-11 11:11

Example output:

id  creator     name       count      timestamp
2   3           AlbAlb     3          2011-11-11 11:11
1   1           BlaBla     2          2011-11-11 11:11
3   2           LbaLba     1          2011-11-11 11:11

Upvotes: 3

Views: 74

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657797

Try this:

SELECT t.id, t.creator, t.name, count(*) AS ct, t.timestamp
FROM   threads t
JOIN   posts p ON p.replyTo = t.id
GROUP  BY 1
ORDER  BY count(*) DESC, 1,2;

Produces exactly the requested results.
See the working demo here.

GROUP  BY 1

is short for (because it is enough to group by the primary key in mysql):

GROUP  BY 1,2,3,5

which is short for (because these are positional parameters):

GROUP  BY t.id, t.creator, t.name, t.timestamp

Answer to additional question in comment

Add a WHERE clause like this:

SELECT t.id, t.creator, t.name, count(*) AS ct, t.timestamp
FROM   threads t
JOIN   posts p ON p.replyTo = t.id
WHERE  t.timestamp BETWEEN (CURRENT_TIMESTAMP - INTERVAL 24 HOUR)
                       AND CURRENT_TIMESTAMP 
GROUP  BY 1
ORDER  BY count(*) DESC, 1,2;

Upvotes: 2

Ariel
Ariel

Reputation: 26773

SELECT Threads.*, COUNT(*) post_count
FROM Threads LEFT JOIN Posts ON Threads.id = Posts.replyTo
GROUP BY Threads.id /* Technically you are supposed to list all columns here, but in MySQL it works fine to just list the primary key */
ORDER BY post_count

Method two:

SELECT *, (SELECT COUNT(*) FROM Posts WHERE Threads.id = Posts.replyTo) post_count
FROM Threads
ORDER BY post_count

Note: Your column names are not good. You should not name all the ids the same, it makes it harder than necessary to join tables. Change replyTo to thread_id, change the id of threads to thread_id, and posts to post_id.

Upvotes: 1

Jake Feasel
Jake Feasel

Reputation: 16955

SELECT 
 t.id, t.creator, t.name, count(p.id) as count, t.timestamp
FROM
 threads t
  INNER JOIN posts p ON
    t.id = p.replyTo
GROUP BY
 t.id, t.creator, t.name, t.timestamp
ORDER BY 
 count DESC

Upvotes: 1

Adam Wenger
Adam Wenger

Reputation: 17560

SELECT t.id, t.creator, t.name, COUNT(1) AS postCount, t.timestamp
FROM threads AS t
INNER JOIN posts AS p ON t.id = p.replyTo
GROUP BY t.id, t.creator, t.name, t.timestamp
ORDER BY COUNT(1) DESC

Upvotes: 1

Related Questions