How to list the most expensive and the cheapest item in each category in Oracle SQL?

So I need to get a list of categories and then get the most expensive item and then the cheapest item out of each category. Then I need to sort these categories by the sum of min and max price in ASC order. I’ve tried this:

SELECT k.name AS Category, p.name AS MostExpensive, p.name AS Cheapest, Max(price),Min(price)
FROM category k, item p
WHERE  p.category_id=k.category_id 
GROUP BY k.name,p.name
ORDER BY Min(price)+Max(price) ASC;

Now this partially works. It does give me a list of categories ordered, it gives me price of the cheapest and most expensive item . But the problem is that it doesn’t do it in one row. For example, I get this in the first 2 rows:

Anyways, I need this table to look different, I need one row in which there will be category CD, then listed CD-RW as the most expensive item in a column right next to CD, and then right next to that another column with CD-R as the cheapest item. Basically, this same thing but only in 1 row .

I’d really appreaciate if somebody could help me out with this

Answer

Try keep clause as following:

SELECT k.name as cat_name,
       max(p.name) keep (dense_rank last order by price) as expensive_item,
       max(price) as max_price,
       max(p.name) keep (dense_rank first order by price) as cheapest_item, 
       min(price) as min_price
FROM category k join item p
On (p.category_id=k.category_id)
GROUP BY k.name
ORDER BY Min(price)+Max(price);

Cheers!!