Question: I'm starting to learn SQL and I'm working on this exercise: I have a "books" table which holds the info on every book (including price and genre ID). 
I need to get the name of the genre which has the highest average price. I suppose that I first need to group the prices by genre and then retrieve the name of the highest..

I know that I can get the results GENRE VS COST with the following:

```
select b.genre, 
       round(avg(b.price),2) as cost 
from books b 
group by b.genre;

```

My question is, to get the genre with the highest AVG price from that result, do I have to make:

```
select aux.genre 
from (
   select b.genre, 
          round(avg(b.price),2) as cost 
   from books b 
   group by b.genre
) aux 
where aux.cost = (select max(aux.cost) 
                  from (
                      select b.genre, 
                             round(avg(b.price),2) as cost 
                      from books l 
                      group by b.genre
                  ) aux);

```

Is it bad practice or isn't there another way? I get the correct result but I'm not confortable with creating two times the same selection.

I'm not using PL SQL so I can't use variables or anything like that..

Any help will be appreciated. 
Thanks in advance!

Answer: 
On Sql server, you can use the avg aggragate inside the windowing function row\_number

```
 with m as(
    select genre, 
         avg(price) cost, 
         row_number() over(order by avg(price) desc) rw
    from books
    group by genre
 )
 select * from m
 where rw=1

```
In MS-SQL you can do this:

```
SELECT TOP 1 genre, ROUND(AVG(price),2) AS cost 
FROM books
GROUP BY genre
ORDER BY ROUND(AVG(price),2) DESC

```

(the TOP clause is MS-SQL specific, but there are similiar constructs in other DBMS)