Is there a way to find max values of three column in mysql-CodePudding

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	Take Me	Take Me 2	15	20	8	Bank B
3	Take Me	Take Me 2	20	25	45	Bank C
4	Take Me	Take Me 2	35	12	4	Bank D
5	GET Me	GET Me 2	11	6	12	Bank A
6	GET Me	GET Me 2	15	45	23	Bank B
7	GET Me	GET Me 2	22	67	35	Bank C
8	GET Me	GET Me 2	36	6	7	Bank D

Example output:

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

The output may be a wrong structure bu forgive my lack of experience. Every 4 rows must be compared in itself I use BS4 TO fetch data , MYSQL to store the data and PYTHON to process data

CodePudding user response：

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;

CodePudding user response：

You can solve this in next way:

first get min of each of rates
second self joins

with `lowest` as (
  select 
    min(`rate_home`) `lowest_home`, 
    min(`rate_vehicle`) `lowest_vehicle`, 
    min(`rate_need`) `lowest_need`
  from `rates`
) select
    concat(`lowest_home`, ' at ', `lh`.`bankname`) `lowest_home`,
    concat(`lowest_vehicle`, ' at ', `lv`.`bankname`) `lowest_vehicle`,
    concat(`lowest_need`, ' at ', `ln`.`bankname`) `lowest_need`
from `lowest`
join `rates` `lh` on `lh`.`rate_home` = `lowest_home`
join `rates` `lv` on `lv`.`rate_vehicle` = `lowest_vehicle`
join `rates` `ln` on `ln`.`rate_need` = `lowest_need`;

sqlize

Result:

 ============= ================ ============= 
| lowest_home | lowest_vehicle | lowest_need |
 ============= ================ ============= 
| 1 at Bank A | 1 at Bank C    | 1 at Bank B |
 ------------- ---------------- -------------

CodePudding user response：

this can be done in sql side

SELECT [Other Fields],
  (SELECT Max(v) 
   FROM (VALUES (rate_home), (rate_vehicle), (rate_need)) AS value(v)) as [MaxRate]
FROM [YourTableName]