CODEWITHSUNDEEP
mysqlsqlsql-serverdatabase
Quan Pham
Quan Pham

Reputation: 33

Get the Post with highest score for each user

Given these 2 table:

Users:

Name    UserId  
-----------
Pump 1  1   
Pump 1  2
Pump 1  3
Pump 2  4
Pump 2  5
Pump 2  6

Posts:

PostId      UserId  Score

1           3       5
2           1       8
3           3       3
4           1       2
5           2       8
6           2       1

How do I get the post with the highest score made by each users?

Best I can do is:

select Users.UserId as UserID, Posts.PostId as PostsID, Max(Posts.Score) as Score
from Users
inner join Posts on Posts.UserId = Users.UserId
Group by Users.UserId, Posts.PostsId

which doesn't gave me the wrong answer.

Upvotes: 0

Views: 159

Answers (4)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT u.Name,
       u.UserId,
       p.PostId,
       p.Score
FROM Users u
  JOIN Posts p
    ON p.UserId = u.UserId
  LEFT JOIN Posts p1
    ON p1.UserId = p.UserId
    AND p1.Score > p.Score
WHERE p1.Score is null

Result:

|   NAME | USERID | POSTID | SCORE |
|--------|--------|--------|-------|
| Pump 1 |      3 |      1 |     5 |
| Pump 1 |      1 |      2 |     8 |
| Pump 1 |      2 |      5 |     8 |

Upvotes: 1

slavoo
slavoo

Reputation: 6076

You need only table Posts to select this result.

select p1.UserId, p1.PostId, p1.Score
from Posts p1
--  inner join Users u on p1.UserId = u.UserId
where not exists 
   (select * 
    from Posts p2 
    where p1.UserId = p2.UserId and p1.Score < p2.Score);

Upvotes: 1

Amarnath Balasubramanian
Amarnath Balasubramanian

Reputation: 9460

Select T.PostId,
       S.UserID,
       S.MAXSCORES
From TBL1 T INNER JOIN 
(SELECT MAX(SCORE) MAXSCORES
           ,UserID
     FROM TBL1 Group BY UserID) S On
S.UserID= t.UserID and s.MAXSCORES= t.score;

Fiddle Demo

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460158

If you're using SQL-Server you can use a ranking function like ROW_NUMBER or DENSE_RANK:

WITH CTE AS
(
  SELECT u.UserId as UserID, p.PostId as PostsID, p.Score,
         RN = ROW_NUMBER() OVER (PARTITION BY u.UserId  ORDER BY Score DESC)
  FROM dbo.Users u
  INNER JOIN dbo.Posts p on p.UserId = u.UserId
)
SELECT UserID, PostsID, Score
FROM CTE
WHERE RN = 1

DEMO

In MySql this should work:

SELECT Users.userid     AS UserID, 
       Posts.postid     AS PostsID, 
       Max(Posts.score) AS Score 
FROM   Users 
       INNER JOIN Posts 
               ON Posts.userid = Users.userid 
GROUP  BY Users.userid

Although the PostID is somewhat arbitrary here.

DEMO

Upvotes: 3

Related Questions