1

Given I have the following table "PriceRecord":

| PriceRecord_ID | Company_ID | Price | Tarif_type | Tarif_Model | ... attributes |
|:--------------:|:----------:|:-----:|:----------:|:----------:|:--------------:|
|        1       |      A     |   10  |   tarif_1  |   Model_1   |       ...      |
|        2       |      A     |   20  |   tarif_2  |   Model_1   |       ...      |
|        3       |      A     |   10  |   tarif_3  |   Model_2   |       ...      |
|        4       |      B     |   11  |   tarif_1  |   Model_2   |       ...      |
|        5       |      B     |   15  |   tarif_2  |   Model_3   |       ...      |
|        6       |      C     |   10  |   tarif_1  |   Model_4   |       ...      |

My goal is to get the min(Price) for each Company. For each min(Price) I need other attributes (e.g. Tarif_type, Tarif_name) as well.

Expected result:

| PriceRecord_ID | Company_ID | Price | Tarif_type | Tarif_Model | ... attributes |
|:--------------:|:----------:|:-----:|:----------:|:----------:|:--------------:|
|        1       |      A     |   10  |   tarif_1  |   Model_1   |       ...      |
|        4       |      B     |   11  |   tarif_1  |   Model_1   |       ...      |
|        6       |      C     |   10  |   tarif_1  |   Model_2   |

I know how to get the min(Price) and group by Company. My Problem is, that I cannot join the other attributes (since the company name is not a unique key.

I tried this query (with the intent to join the table IF both conditions are met --> unfortunately it joins two times for each condtion and I have more than one record for each company in my result table)

    subquery = db.session.query(PriceRecord.company_id, db.func.min(PriceRecord.Price).label("minPrice")) \
        .group_by(PriceRecord.company_id) \
        .subquery()
    result = db.session.query(subquery.c.company_id, subquery.c.minPrice, PriceRecord.tarif_type, PriceRecord.tarif_model) \
    .join(PriceRecord, subquery.c.insurance_company==PriceRecord.company_id and subquery.c.minPrice==PriceRecord.Price) \

I have found this similar solution but couldn't implement it for my scenario.

Any Help is highly appreciated! thank you in advance

Simon
  • 317
  • 3
  • 17
  • You are not using "company" clearly. You seem to be saying that the Company column is really company name. If company name is not unique, how could you possibly get info per company? You can only get info per company name. You need to have the company ids accompany the names. – philipxy May 13 '18 at 04:35
  • Thank you for your input. I clarified the description above. Company is a foreign key of another table. but my problem is, that a company can have multiple price records with the same min(Price). In this case a just want one Record sho show up in my result's table (it doesent really matter which one, e.g. the first which is found or which is the easiest to implement). The table has 250k of price records and otherwise it gets messy very fast. thank you for your time – Simon May 13 '18 at 07:52

2 Answers2

0
SELECT b.*
FROM (
  SELECT Company, MIN(Price) Price FROM PriceRecord GROUP BY Company
  ) a
JOIN PriceRecord b on a.Company = b.Company and a.Price = b.Price

One caveat - If Company A has two rows with a price of 10, both will return.

JoeLeBaron
  • 338
  • 1
  • 3
  • 12
  • your solution is correct, but unfortunately most of the companies have multiple price records with the same price -> this was the reason I got multiple entries in my result table. Is there a possibility to get only the first row with min(Price) per company? – Simon May 13 '18 at 01:48
  • @Simon What does "first" mean? First when ordered by what? Anyway there's no per company here, only per Company. – philipxy May 13 '18 at 04:36
  • it doesen't really matter which of the found min(Prices) and there corresponding attributes show up in the results table. whichever is the easiest to implement. thank you very much for your help – Simon May 13 '18 at 07:54
  • Change the SELECT to: SELECT DISTINCT b.Company, b.Price That will get you just one row per company with the minimum price for that company. – JoeLeBaron May 14 '18 at 22:01
0

After some research I was able to get the expected result with the following sql code:

select * 
from
 (
  select
    row_number () over (partition by company_ID order by premium asc) as rownumber, 
    min(Price) over (partition by company_ID) as minimalPrice, *
  FROM price_table
 ) subquery
where subquery.rownumber = 1

With the so called window functions you are able to use aggregate functions without the group_by statement. The rownumber function assigns a "on the fly"-created number to each price record (due to the partition function the rownumber starts from 1 again for each company). Because I need exact one result for each company I filter by rownumber = 1.

Here an example of my inner select (without filtering where rownumber equals = 1)

rownumber minprice company_id  price
   1       408.9      8         408.9
   2       408.9      8         436.1
   3       408.9      8         439.7
   4       408.9      8         463.1
   5       408.9      8         468.9
   6       408.9      8         490.3
   7       408.9      8         498
   8       408.9      8         517.5
   9       408.9      8         527.2
   10      408.9      8         528.2
   11      408.9      8         556.4
   12      408.9      8         568
   1       364.4      32        364.4
   2       364.4      32        387.6
   3       364.4      32        391.8
   4       364.4      32        416.8
   5       364.4      32        419.3
   6       364.4      32        446
   7       364.4      32        446.6
   8       364.4      32        474.1
   9       364.4      32        475.1
   10      364.4      32        485
   11      364.4      32        504.3
   12      364.4      32        515.9
   1       412        57        412
   2       412        57        433.7
   3       412        57        439.7

I hope this helps, it took me some time to dig into those sql functions.

Simon
  • 317
  • 3
  • 17