Oracle ORDER BY with rownum or HAVING >= ALL

Question

My database teacher asked me to write (on Oracle Server) a query: select the groupid with the highest score average for year 2010

I wrote:

SELECT * FROM (
    SELECT groupid, AVG(score) average FROM points
    WHERE yr = 2010
    AND score IS NOT NULL
    GROUP BY groupid
    ORDER BY average DESC
) WHERE rownum = 1;

My teacher tells me that this request is "better":

SELECT groupid, AVG(score) average FROM points
WHERE yr = 2010
GROUP BY groupid
HAVING AVG(score) >= ALL (
    SELECT AVG(score) FROM points
    WHERE yr = 2010
    GROUP BY groupid
);

Which one is the fastest/better ? Is there any better solution too (for Oracle only) ? Thanks.

Answer 1

There are two reasons your instructor is telling you that.

1. Data model. Relational DBMSs deal with sets, not lists. If you are learning SQL, it is better for you to think in terms of sets of tuples, that are unordered, than in order lists. You will be better at understanding how to query the DBMS. I consider your solution a hack: one that works, partially, since--as Perun_x has pointed out--- it does not work if more than one tuple match the result. It is contrary to the data model and spirit of SQL).

2. Portability. This is the real killer. Your code will work on Oracle but not in other DBMSs that do not support the row_number attribute (each has its own way to do it).

--dmg