CODEWITHSUNDEEP
mysql
Marcus Price
Marcus Price

Reputation: 39

Ordering forum threads by most viewed count in MySQL

I have two tables of which are:

threads {
 `id` int(11) PRIMARY KEY
 `by` int(11)
 `forum` int(11)
 `title` text 
 `content` text
 `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP }

views {
 `thread_id` int(11)
 `forum_id` int(11)
 `user_id` int(11)
 `time_viewed` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP }

My aim is to select the threads with the most views, in order, using these two tables.

I tried researching but i can't even wrap my head around how to do this in SQL.

Upvotes: 1

Views: 47

Answers (3)

CiprianD
CiprianD

Reputation: 512

SELECT t.*,
       COUNT(v.thread_id)
FROM threads t
JOIN views v ON t.id = v.thread_id
GROUP BY v.thread_id
ORDER BY v.thread_id DESC

if you want only threads that have a higher view count than x then you can add the following at the end

HAVING COUNT(v.thread_id) >= x

if you need the TOP 10 threads then you should add this at the end (instead of the previous rule)

LIMIT 10

Upvotes: 1

mongotop
mongotop

Reputation: 5774

SELECT T.id, COUNT(TV.thread_id)
FROM threads T LEFT JOIN views TV ON T.id = TV.thread_id
ORDER BY COUNT(TV.thread_id) DESC
GROUP BY T.id

You could add a LIMIT 10 to the end of the statment to get the top 10 most popular threads.

Upvotes: 0

J.K.Lauren
J.K.Lauren

Reputation: 316

Something like this should do the trick:

SELECT t.id, count(v.thread_id) as count from threads t, views v where t.id=v.thread_id group by t.id order by count DESC;

Upvotes: 2

Related Questions