StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>From your comments: </p> <blockquote> <p><i>calculate average rating for each mid (with a GROUP BY mid), then choose maximum and return the mid</i></p> </blockquote> <p>So first step, calculate average for each mid:</p> <pre><code>select mid, avg(rating) as avg_rating from rating group by mid; </code></pre> <p>Now choose the maximum:</p> <pre><code>select max(avg_rating) from ( select avg(rating) as avg_rating from rating group by mid ) as mar </code></pre> <p>Now combine these:</p> <pre><code>select ar.mid, mar.max_avg from ( select mid, avg(rating) as avg_rating from rating group by mid ) as ar join ( select max(avg_rating) as max_avg from ( select avg(rating) as avg_rating from rating group by mid ) as t ) as mar on ar.avg_rating = mar.max_avg; </code></pre> <p>SQLFiddle example (using Postgres, but works with HSQLDB as well): <a href="http://sqlfiddle.com/#!12/e208a/8" rel="nofollow">http://sqlfiddle.com/#!12/e208a/8</a></p> <p>It's not the most simple solution but quering on grouped data never is. Using the <code>TOP</code> construct as shown by Luther is going to be much faster. The only drawback with the <code>TOP 1</code> is that you won't notice if two movies have the same average rating.</p> <p><strong>Edit</strong>: Just to expand a little bit beyond HSQLDB. In a database that supports window functions (PostgreSQL, Oracle and many others), this type of question is very easy:</p> <pre><code>select * from ( select mid, avg(rating) as avg_rating, dense_rank() over (order by avg(rating) desc) as rnk from rating group by mid ) t where rnk = 1; </code></pre> <p>It is especially easy to find the second highest, third highest and so on (<code>where rnk = 2</code>, <code>where rnk = 3</code>) which is really complicated using those nested queries - but a lit bit easier when using the <code>TOP/LIMIT</code> aproach.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload