Hello. Tell me, please, how best to join 2 tables:

Cars

  • mark
  • power

Car categories

  • name of category
  • min_power

As a result, should be selected:
- brand
- name of category

Selection under the condition that power> = min_power in categories. Everything is complicated by the fact that if power = 150, then the category should be the one where min_power = 120 (but under the condition> = 150 several categories fall). Plus, maybe the power is less than the minimum power in the categories and you need to return an empty string or zero.

Theoretically, there are not so many rows and you can make a subquery for each row, but it seems to me that this is not really ice at all.

    2 answers 2

    with cte as ( select car.mark /* ΠΌΠ°Ρ€ΠΊΠ° */ , car.power /* ΠΌΠΎΡ‰Π½ΠΎΡΡ‚ΡŒ */ , cat.cat /* Π½Π°Π·Π²Π°Π½ΠΈΠ΅ ΠΊΠ°Ρ‚Π΅Π³ΠΎΡ€ΠΈΠΈ */ , cat.min_power /* ΠΌΠΈΠ½_ΠΌΠΎΡ‰Π½ΠΎΡΡ‚ΡŒ */ , rank() over (partition by car.mark order by cat.min_power desc) rnk from car /* Автомобили */ left join cat /* ΠšΠ°Ρ‚Π΅Π³ΠΎΡ€ΠΈΠΈ_автомобиля */ on car.power >= cat.min_power ) select cte.mark , cte.power , cte.cat , cte.min_power from cte where cte.rnk = 1;

      Let for example the following database

       CREATE TABLE cars ( title text, power int ); CREATE TABLE categories ( title text, power int ); INSERT INTO cars VALUES ('kia', 120), ('kamaz', '1000'), ('bike', 20); INSERT INTO categories VALUES ('auto', 100), ('truck', 500);

      cars is a table of cars, and categories are their types. cars.title is the car name, cars.title is the power, categories.title is the name of the category, and categories.power is the minimum power to fall into the category.

      Then you can match the brand of the car and the category using the following query

       SELECT cars_power.title AS car, categories.title FROM (SELECT cars.title, MAX(categories.power) AS power_min FROM cars LEFT JOIN categories ON cars.power >= categories.power GROUP BY cars.title) AS cars_power LEFT JOIN categories ON cars_power.power_min = categories.power;

      As a result, we get

        car | title -------+------- kia | auto kamaz | truck bike |
      • How about maybe the power is less than the minimum power in the categories and you need to return an empty string or zero. ? - Akina
      • @Akina thanks for the comment, corrected the request. - cheops