SQL command to get field of a maximum value, without making two select
- by António Capelo
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!
