Does "SELECT name, MAX(score)" sort for the name with the max score?

Data Science
1

Hi, could you fix my understanding? The code below is the eight task of the Joining Multiple Tables exercises, Multible Tables With Reddit. Link at end.

In my eyes, the SELECT function for (1) ‘subreddit,name’, and (2) max(posts.score) are distinct. So, I would think the result would comprise the alphabetically-first names within the GROUP BY function and the highest scores within the GROUP BY function.

However, the query returns the (1) name of the highest score post, and (2) the highest score. I.e., the alphabetically first name is not returned, and the query sorts for the name of the highest score. How does this work? Am I right in that this has to do with the GROUP BY function? thanks!

SELECT 
  posts.title, 
  subreddits.name AS 'subreddit_name', 
  MAX(posts.score) AS 'highest_score'
FROM posts
INNER JOIN subreddits
  ON posts.subreddit_id = subreddits.id
GROUP BY subreddits.id;

Link to exercise: https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-multiple-tables-with-reddit

2

Hello, and welcome to the forums!

I might not fully understand your question, so please let me know if I’m off the mark.

I’m not seeing any sorting at all for any of the columns when I run your query:

title subreddit_name highest_score
Running DOOM on a toaster programming 143728
Clean water ice found just below Mars’ surface science 49477
I edited myself into a movie scene funny 43894
Quarter Life 3 being developed by Pipe Corporation gaming 44695
My dog when she was born and 10 years later pics 48294
Promising advances made toward cure for cancer news 136532

Although it wasn’t required by the description of the task, I used an ORDER BY in my solution to sort the highest score in descending order.

3

SELECT
posts.title,
subreddits.name AS ‘subreddit_name’,
(SELECT MAX(score) FROM posts WHERE posts.subreddit_id = subreddits.id) AS ‘highest_score’
FROM posts
INNER JOIN subreddits
ON posts.subreddit_id = subreddits.id
GROUP BY subreddits.id; i tried this and got the answer.