Use ROWNUM to return multiple non-aggreate GROUP BY columns

Somebody needs to explain something to me.  Why in the world can’t we use some of the aggregate functions without having to GROUP BY every other column?  I know things like SUM and AVG won’t work, but why can’t I do the following:

SELECT name, toy_category, Max(cool_factor) FROM toys GROUP BY toy_category

And have it just return the coolest toys?  Now that is a very simplistic example, but I needed to do something very similar to this.  The best way I found was to use Oracle’s ROWNUM column on a sorted resultset to allow me to join back to said resultset.  This also seems like the perfect time to use a WITH Clause (my other post about using WITH Clause) since we need to hit that data several times and preserve the ROWNUM.  The first thing we need to do is get the list of Toys into a named query ordered by the column we want to Max() – the order is very important:

WITH temp_all_toys AS (SELECT name, toy_category, cool_factor FROM toys ORDER BY cool_factor)

Now that we have all the toys in a discreet list we can look for the Max(ROWNUM) for each toy_category.

SELECT Max(rownum) as max_rownum FROM temp_all_toys GROUPY BY toy_category

The output of that query is pretty dull.  Just a bunch of random looking numbers…but they are really the “primary key” for our temp table so if we join them back to the temp_all_toys table, we can get at any of the columns!  Thanks for nothing GROUP BY.  There is one last trick I found for joining back: for some reason my query wouldn’t work unless I joined to a second subquery to get at the ROWNUM:

WITH temp_all_toys AS (SELECT name, toy_category, cool_factor FROM toys ORDER BY cool_factor)

SELECT
  all_recs.*
FROM
  (SELECT rownum as rnum, name, toy_category FROM temp_all_toys GROUPY BY toy_category) all_recs
JOIN
  (SELECT Max(rownum) as max_rownum FROM temp_all_toys GROUPY BY toy_category) max_recs
ON all_recs.rnum = max_recs.max_rownum