<p>I have a list of stores, departments within the stores, and sales for each department, like so (created using max(sales) in a subquery, but that's not terribly important here I don't think):</p>

<pre><code>toronto    baskets 500
vancouver  baskets 350
halifax    baskets 100
toronto    noodles 275
vancouver  noodles 390
halifax    noodles 120
halifax    fish    200
</code></pre>

<p>I would like to ask for the highest-selling department at each store. The results should look like this:</p>

<pre><code>toronto    baskets 500
vancouver  noodles 275
halifax    fish    200
</code></pre>

<p>Whenever I use GROUP BY, it includes all the listings from my subquery. Is there a nice clean way to do this without a temporary table?</p>

This works in Oracle, other implementations may have different syntax for analytic functions (or lack them entirely):
```
```
select store
     , max(department) keep(dense_rank last order by sales)
     , max(sales)
  from (
        ...query that generates your results...
       )
 group by store
```
```