Oracle SQL select highest values from different tables with "in line view"

Question

I have a database containing product information in the two tables "prod_group" and "product". There are 4 different product groups containing mutiple products. I am supposed to select the most expensive product of each product group by using an "in line view".

The code I have so far:

select *
from
(
select distinct pg.group_name, p.price most_expensive
from prod_group pg, product p
where pg.group_id = p.group_id
group by pg.group_name, p.price
order by p.price desc
)
where rownum < 5;

The problem with above code is that even though I use "distinct" the 4 products printed out are the 4 most expensive products in the entire database and not the most expensive for each of the 4 product groups.

OldProgrammer · Accepted Answer · 2015-09-16T15:28:56.543

0

select  pg.group_name, max(p.price) 
from prod_group pg, product p
where pg.group_id = p.group_id
group by pg.group_name
order by pg.group_name

SQLFiddle

edited Sep 16 '15 at 15:28

answered Sep 16 '15 at 15:14

OldProgrammer

12,050
4
24
45

Thanks for this very clean solution! – Mongzyy Sep 16 '15 at 15:36
Er, I fail to see how this selects the most expensive product per group (or any product at all for that matter). I also don't see any inline view either. – Thorsten Kettner Sep 16 '15 at 19:20

score 0 · Answer 2 · answered Sep 16 '15 at 15:17

This will get the most expensive prices for each group name:

select group_name,
       price AS most_expensive
from
(
  select pg.group_name,
         p.price,
         ROW_NUMBER() OVER ( PARTITION BY pg.group_name ORDER BY p.price DESC ) AS rn
  from   prod_group pg
         INNER JOIN
         product p
         ON ( pg.group_id = p.group_id )
)
where rn = 1;

score 0 · Answer 3 · answered Sep 16 '15 at 19:18

SQL is all about what data to get, not how to get it. So it is uncommon to be asked to write a query using a particular technique. Hence I surmise that it's a teacher asking this of you? But then: You are using an old join syntax that was replaced more than twenty years ago. No teacher would teach you that now anymore, I think.

Well, an inline view is the old Oracle name for what's now generally called a derived table, i.e. a subquery in the FROM clause. To write a query with a derived table is a strange request, because you can turn any query into such format: select * from (<some query here>).

You are asked to get the products that have the highest price in their product group. There are two tables, but all data needed, the product and its group, are found in one of them, the table product. No need to select from the other table prod_group. The typical straight forward query would be:

select *
from product where (group_id, price) in
(
  select group_id, max(price)
  from product
  group by group_id
);

This doesn't use a derived table, though, but can be re-written such with the method given above. However, we can replace the IN clause with a join and then we get to a derived table much more naturally:

select p.*
from product p
join 
(
  select group_id, max(price) as price
  from product
  group by group_id
) pmax on pmax.group_id = p.group_id and pmax.price = p.price;

Another way would be to use the analytic MAX function, which would also avoid to have to select from the same table twice:

select *
from
(
  select 
    p.*,
    max(price) over (partition by group_id) as maxprice
  from product p
)  
where price = maxprice;

Here we even need the derived table, because we cannot put the analytic function in the WHERE clause where we would like to have it. We must use it in a subquery and put the condition in the outer query hence.

Oracle SQL select highest values from different tables with "in line view"

3 Answers3