0

I am comparing from different table to get the COLUMN_NAME of the MAXIMUM value

Examples. These are example tables: Fruit_tb, Vegetable_tb, State_tb, Foods_tb

Under Fruit_tb fr_id fruit_one fruit_two 1 20 50

Under Vegetables_tb (v = Vegetables) v_id v_one V_two 1 10 9

Under State_tb stateid stateOne stateTwo 1 70 87

Under Food_tb foodid foodOne foodTwo 1 10 3

Now here is the scenario, I want to get the COLUMN NAMES of the max or greatest value in each table.

Cœur
  • 37,241
  • 25
  • 195
  • 267
yormen
  • 188
  • 2
  • 11

1 Answers1

0

You can maybe find out the row which contains the max value of a column. For eg:

SELECT fr_id , MAX(fruit_one) FROM Fruit_tb GROUP BY fr_id;

In order to find the out the max value of a table:

SELECT fr_id ,fruit_one FROM Fruit_tb WHERE fruit_one<(SELECT max(fruit_one ) from Fruit_tb) ORDER BY fr_id DESC limit 1;

A follow up SO for the above scenario.

Maybe you can use GREATEST in order to get the column name which has the max value. But then what I'm not sure is whether you'll be able to retrieve all the columns of different tables at once. You can do something like this to retrieve from a single table:

SELECT CASE GREATEST(`id`,`fr_id`)
         WHEN `id` THEN `id`
         WHEN `fr_id` THEN `fr_id`
         ELSE 0
       END AS maxcol,
       GREATEST(`id`,`fr_id`) as maxvalue FROM Fruit_tb;

Maybe this SO could help you. Hope it helps!

Community
  • 1
  • 1
Kulasangar
  • 9,046
  • 5
  • 51
  • 82
  • thanks for your comment, but, i want to get the column name – yormen Mar 04 '17 at 16:46
  • so i used the dsc and asc to arrange interms of highest and with the column name as well.....dont know if this is clear enough. – yormen Mar 04 '17 at 16:49