CODEWITHSUNDEEP
sqloracle-database
Юра Ярмак
Юра Ярмак

Reputation: 11

how to write this query correctly

for each category show the equipment cheapest in this category it was my attempts to solve it, however I don't know what to write further

SELECT category_of_equipment, name_of_equipment, price
from equipment
where price = ( select min(price) 
from equipment

the table

CREATE TABLE equipment (
    id int NOT NULL,
    price int NOT NULL,
    name_of_equipment varchar(228) NOT NULL,
    category_of_equipment int NOT NULL,
    CONSTRAINT equipment_pk PRIMARY KEY (id)
);

Upvotes: 1

Views: 37

Answers (3)

Amit Gupta
Amit Gupta

Reputation: 252

You can use self join -

select 
    a.*
from 
equipment a JOIN
(
   select category_of_equipment,min(price) minPrice from equipment group by price
) b
where 
a.category_of_equipment = b.category_of_equipment and a.price = b.minPrice

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

Rank equipment by price (partitioned by category) and return rows whose rank = 1.

That should be somewhat better than a correlated subquery because this option selects data from the equipment table only once, while correlated subquery has to select it twice (once in a subquery, and once again to return desired data).

WITH
   ranker
   AS
      (SELECT e.*,
              RANK ()
                 OVER (PARTITION BY category_of_equipment ORDER BY price ASC) rnk
         FROM equipment e)
SELECT r.id,
       r.price,
       r.name_of_equipment,
       r.category_of_equipment
  FROM ranker r
 WHERE r.rnk = 1;

Upvotes: 1

Serg
Serg

Reputation: 22811

You can use a correlated subquery

select category_of_equipment, name_of_equipment, price
from equipment t
where price = ( select min(price) 
                from equipment t2 
                where t2.category_of_equipment = t.category_of_equipment);

Upvotes: 0

Related Questions