Question 113626 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

3

Answers


  1. 0

    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]
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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 |
+-------------+----------------+-------------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search