Order by desc vs min/max

Get Help SQL
,
#1

Hi everyone,
I am attempting this extra practice
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

and wanted to ask about problem 9.
My code is as follows

WITH total_posts AS(
  SELECT * FROM posts
  UNION
  SELECT * FROM posts2
)
SELECT title,score,subreddits.name FROM total_posts
JOIN subreddits ON
total_posts.subreddit_id = subreddits.id
GROUP BY subreddits.id
ORDER BY 2 DESC;

However, it doesn’t query the correct results, not unless I use a max(score). So my question is, what is happening. Is it randomly selecting a post from a subreddit?

#2

You would want to use an (inner) join with this problem.
Ex:

INNER JOIN subreddits ON
posts.title = subreddits.name

I ran your code & it produces scores in descending order.

This is interesting b/c the question doesn’t ask the user to utilize the combined results from the previous union of the two post tables. Rather, it just asks about ONE table.