Oracle ORDER BY with rownum or HAVING >= ALL
average, having, rownum, select, sql
Solution
There are two reasons your instructor is telling you that.
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).
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
Problem
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.