Hi,
I can give you example how to do something like this in
SQL Server (should be very similar in
MySQL too).
First, let’s say that you have table named UserAndArticle with the following data:
ID UserID ArticleID
1 10 101
2 10 102
3 10 103
4 20 101
5 20 103
6 30 101
7 30 103
8 40 101
9 40 102
10 40 103
11 50 102
12 50 103
13 60 101
14 60 102
15 70 101
16 70 102
17 70 104
18 70 105
19 70 106
SQL Server script would be something like this (in
MySQL you need to change TOP with LIMIT clause):
DECLARE @ArticleID INT = 102
SELECT TOP 3 t2.ArticleID,
COUNT(t2.ArticleID) AS NoOfViews
FROM UserAndArticle AS t1
INNER JOIN UserAndArticle AS t2
ON t1.UserID = t2.UserID
WHERE t1.ArticleID = @ArticleID
AND t2.ArticleID <> @ArticleID
GROUP BY t2.ArticleID
ORDER BY NoOfViews DESC
In this example, I am searching the best 3 articles by the number of views who read article 102.
Result:
ArticleID NoOfViews
101 4
103 3
105 1
Also, you may want to eliminate current user ArticleIDs from the result.
I hope this was helpful.