Skip to content
Advertisement

how to select the max of COUNT(*) (SQL postgresql)

I switched from NOSQL to SQL and i can’t find how to select the max of count(*)

i created users table, posts and comments. i want to select TOP 10 users with the max of posts and max of comments

Advertisement

Answer

First, using SQL database require you to define the JOIN statement between the associated tables to get the referenced relationship data.

Since you require to get TOP 10 user with most posts, using traditional approach with LIMIT 10 will have problem when users have the same post count and should be considered the same rank. E.g: 4 people have 5 posts, 3 people have 6 posts..

To solve the same post count problem above, we will choose RANK query, this will treat people with the same post count as the same rank.

Breaking to small pieces:

1.Get post count for each user

First, we get the post count for each user, this require the COUNT function, and GROUP BY author_id

2. Calculating the rank of post count

Use the RANK() function to calculate the rank of the post. Ranking positition is determined by the highest number of total_posts

3. Wrap up

The last step is joining the users table with the posting_rank_stats table to get the result. Since we only want to get in the TOP 10 user with most post, so you will need to add the condition posting_rank_stats.posting_rank <= 10 in the WHERE clause

Apply the same concept to get comment rank also.

Here is the db fiddle I created, combine both posting rank and commenting rank

id fullname posting_rank total_posts comment_rank total_comments
1 Jackson 1 3 5 1
2 Marry 4 1 1 5
3 Josh 4 1 2 3
4 Harley 1 3 4 2
5 Gordon 4 1 5 1
6 Barney 4 1 2 3
7 Gman 4 1
8 Stephan 3 2
9 Lucy
10 Jordan
11 Bill
12 Rosh
13 Lee

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement