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;
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.