Issue

This Content is from Stack Overflow. Question asked by simsar

I have been working on a project which analyze credit rate from different bank.
I have a simple table structured like below

`id` varchar(255) DEFAULT NULL,
`credit_name` varchar(255) DEFAULT NULL,
`credit_name2` varchar(255) DEFAULT NULL,
`rate_home` float DEFAULT NULL,
`rate_vehicle` float NOT NULL,
`rate_need` float NOT NULL,
`bankname` float NOT NULL,

Example input below:
| id | credit_name |credit_name2|rate_home|rate_vehicle|rate_need|bankname|
| —| ———– |———— |———|————|———|——–|
| 1 | Take Me |Take Me 2 |10 | 5 | 15 | Bank A
| 2 | Pick Me |Pick Me 2 |15 | 20 | 8 | Bank B|
| 3 | Choo Me |Choo Me 2 |20 | 25 | 45 | Bank C|
| 4 | Gett Me |Gett Me 2 |35 | 12 | 4 | Bank D|

Example output:

rate_home	rate_vehicle	rate_need
35 from bank d	25 from bank c	45 from bank c

The output may be a wrong structure bu forgive my lack of experience.

I use BS4 TO fetch data , MYSQL to store the data and PYTHON to process data

Solution

Not an easy solution but you can use union for the 3 columns , then use an outer query to get the 1 result for each column:

select max(rate_home) as rate_home,max(rate_vehicle) as rate_vehicle, max(rate_need) as rate_need
from (   
SELECT  concat(t1.rate_home,' from ', t1.bankname) as rate_home ,
        null as rate_vehicle,
        null as rate_need
FROM test t1
LEFT JOIN test t2 ON t1.rate_home < t2.rate_home
WHERE t2.rate_home IS NULL
union
SELECT  null as rate_home,
        concat(t1.rate_vehicle,' from ', t1.bankname) as rate_vehicle ,
         null as rate_need
FROM test t1
LEFT JOIN test t2 ON t1.rate_vehicle < t2.rate_vehicle
WHERE t2.rate_vehicle IS NULL
union
SELECT  null as rate_home,
        null as rate_vehicle,
        concat(t1.rate_need,' from ', t1.bankname) as rate_need 
FROM test t1
LEFT JOIN test t2 ON t1.rate_need < t2.rate_need
WHERE t2.rate_need IS NULL ) as tbl;

https://dbfiddle.uk/0WlUbzT1

Note this doesn’t handle ties

Edit.The op needs the max values for each credit_name group. The above query helped the op and it’s a slight modification from
Slava Rozhnev answer

with `highest` as (
    select credit_name,
           max(`rate_home`) `max_rate_home`,
           max(`rate_vehicle`) `max_rate_vehicle`,
           max(`rate_need`) `max_rate_need`
   from `bank_rate`
    group by credit_name
) select highest.credit_name ,
         group_concat(distinct `max_rate_home`, ' at ', `lh`.`bankname`) `max_rate_home`,
         group_concat(distinct `max_rate_vehicle`, ' at ', `lv`.`bankname`) `max_rate_vehicle`,
         group_concat(distinct `max_rate_need`, ' at ', `ln`.`bankname`) `max_rate_need`
  from `highest`
  join `bank_rate` `lh` on `lh`.`rate_home` = `max_rate_home`
  join `bank_rate` `lv` on `lv`.`rate_vehicle` = `max_rate_vehicle`
  join `bank_rate` `ln` on `ln`.`rate_need` = `max_rate_need`
  group by highest.credit_name;

This Question was asked in StackOverflow by simsar and Answered by Ergest Basha It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.