Oracle SQL Group By where column is not in the group by or has an aggregate function

Question

Suppose we have this:

Category	Item	Price
Truck	Engine	$300
Truck	Lamp	$50
Truck	Brake	$100
Car	Engine	$400

How can we have a SQL statement that produces this? Assuming there is a one to one relationship between Item and Price, so that when we find the cheapest price we also display the item.

SELECT Category, Item, min(Price)

From table

group by Category

---

Can this be done without a subquery?

Can it be done in Python Pandas Group By?

Answer 1

This:

Assuming there is a one to one relationship between Item and Price

means ... what, exactly? That each item has only one price? That's "one-to-one", isn't it? If so, then what's the problem? Query you need is a simple

select category, item, price
from parts;

On the other hand, you wouldn't be saying

so that when we find the cheapest price we also display the item

Cheapest price? So, one item can have multiple prices, after all? That's not one-on-one, then.

Anyway: presuming your table looks like this:

SQL> select category, item, price from parts order by 1, 2;

CATEG ITEM        PRICE
----- ------ ----------
Car   Engine        400
Truck Brake         100
Truck Engine        300         --> two prices for truck's engines
Truck Engine        999         --> (300 being the cheapest)
Truck Lamp           50

Query you wrote seems to be close to what you need - just include item into the group by clause:

SQL> select category, item, min(price) price
  2  from parts
  3  group by category, item
  4  order by category, item;

CATEG ITEM        PRICE
----- ------ ----------
Car   Engine        400
Truck Brake         100
Truck Engine        300
Truck Lamp           50

---

Can this be done without a subquery?

Why? Not all subqueries are evil; the point is not to fetch data from the same table multiple times. Something like this doesn't - only the subquery (in a form of a CTE in this example) fetches data from the parts table; main query (line #6) just fetches rows with lowest price per category and item:

SQL> with temp as
  2    (select category, item, price,
  3       rank() over (partition by category, item order by price) rn
  4     from parts
  5    )
  6  select category, item, price
  7  from temp
  8  where rn = 1
  9  order by category, item;

CATEG ITEM        PRICE
----- ------ ----------
Car   Engine        400
Truck Brake         100
Truck Engine        300
Truck Lamp           50

SQL>

---

Can it be done in Python Pandas Group By?

I have no idea.

---

If that's not what you're looking for, you'll have to explain it better.