Syntax MAX

Databases
1

Hi all, hope in your help.

I’ve this table in mysql:

UNITS	IND1	IND2
AAA	84%	59%
BBB	88%	56%

I need this output with the max value in table:

UNITS	IND
AAA	59%
BBB	88%

I tried this but the valur of units is incorrect:

mysql> SELECT UNITS,
	MAX(IND1) AS ind1, MAX(IND2) as ind2
FROM
	`tbl_max`;
+-------------------------+------+-----+
| UNITS                   | ind1 | ind2|
+-------------------------+------+-----+
| AAA                     | 88%  | 59% |
+-------------------------+------+-----+
1 row in set

Can you help me?
thank you

2

you forgot the GROUP BY clause

3

thank you for suggestion but:

mysql> SELECT
	units,
	MAX(IND1) AS ind1,
	MAX(IND2) AS ind2
FROM
	`tbl_max`
GROUP BY units;
+------------------------------+------+------+
| units                        | ind1 | ind2 |
+------------------------------+------+------+
| BBB                          | 88%  | 56%  |
| AAA                          | 84%  | 59%  |
+------------------------------+------+------+
4 rows in set

I need this:

+------------------------------+------+
| units                        | ind  |
+------------------------------+------+
| BBB                          | 88%  |
| AAA                          | 59%  |
+------------------------------+------+
4

Two queries? :confused:

mysql> SELECT
	units,
	MAX(ind1)
FROM
	`tbl_max`
GROUP BY
	units
ORDER BY
	MAX(ind1) DESC
LIMIT 1;



SELECT
	units,
	MAX(ind2)
FROM
	`tbl_max`
GROUP BY
	units
ORDER BY
	MAX(ind2) DESC
LIMIT 1;
+-------+-----------+
| units | MAX(ind1) |
+-------+-----------+
| BBB   | 88%       |
+-------+-----------+
1 row in set

+-------+-----------+
| units | MAX(ind2) |
+-------+-----------+
| AAA   | 59%       |
+-------+-----------+
1 row in set
5

no :slight_smile:

also, LIMIT 1 is wrong :slight_smile:

6

this is a great example of what happens when your table design violates first normal form – the result is, awkward, clumsy, and inefficient queries

SELECT units
     , MAX(ind) AS maxind
  FROM ( SELECT units
              , ind1 AS ind
           FROM tbl_max
         UNION 
         SELECT units
              , ind2 
           FROM tbl_max ) AS discombobulated
GROUP 
    BY units;
7

I was sure :lol:

also, LIMIT 1 is wrong :slight_smile:

Why is wrong? :rolleyes:

8

because you want more than one unit returned, you want all units returned

9

hi Rudy, thank you for help but the output is always incorrect… :frowning:

mysql> SELECT units
     , MAX(ind) AS maxind
  FROM ( SELECT units
              , ind1 AS ind
           FROM tbl_max
         UNION 
         SELECT units
              , ind2 
           FROM tbl_max ) AS discombobulated
GROUP 
    BY units;
+-------+--------+
| units | maxind |
+-------+--------+
| AAA   | 84%    |
| BBB   | 88%    |
+-------+--------+
2 rows in set
10

please explain what’s wrong with it

also, please explain the correct output, not just with “expected output” that we have to figure out (because obviously, i figured it out wrong), but using words

for example, in post #1, you want the “max” output, but AAA has two values, 84 and 59, and yet somehow you want 59, not 84 – why?

11

okay…

in this table

UNITS	IND1	IND2
AAA	84%	59%
BBB	88%	56%

I need extract the max values of the ind1 and the ind2… for the ind1 the max values is 88% for ind2 the max value is 59%
I’ve got several problems here because the data may not be normalised …

12

yes, you do

13

really can you? how?

14

write a query to find the max ind1 value

then use that query as a subquery in the FROM clause of a new query, and join it back to the table

this allows you to pull whatever unit has the max value

repeat for the other ind

15

okay thank you … I don’t go beyond this query … the value of units in ind 59% is incorrect… AAA instead of BBB …

mysql> SELECT
	units,
	ind
FROM
	(
		SELECT
			units,
			MAX(ind1) AS ind
		FROM
			tbl_max
		UNION
			SELECT
				units,
				MAX(ind2) AS ind
			FROM
				tbl_max
	) AS p;
+-------+-----+
| units | ind |
+-------+-----+
| AAA   | 88% |
| AAA   | 59% |
+-------+-----+
2 rows in set
16

i don’t think you understood what i suggested…

write a query to find the max ind1 value

SELECT MAX(ind1) AS max_ind1
  FROM tbl_max

then use that query as a subquery in the FROM clause of a new query, and join it back to the table 

SELECT t.units
     , t.ind1
  FROM ( SELECT MAX(ind1) AS max_ind1
         FROM tbl_max ) AS m
INNER
  JOIN tbl_max AS t
    on t.ind1 = m.max_ind1

repeat for the other ind

17

okay, your suggestion are two different queries?

mysql> SELECT
	t.units,
	t.ind1
FROM
	(
		SELECT
			MAX(ind1) AS max_ind1
		FROM
			tbl_max
	) AS m
INNER JOIN tbl_max AS t ON t.ind1 = m.max_ind1;

SELECT
	t.units,
	t.ind2
FROM
	(
		SELECT
			MAX(ind2) AS max_ind2
		FROM
			tbl_max
	) AS m
INNER JOIN tbl_max AS t ON t.ind2 = m.max_ind2;
+-------+------+
| units | ind1 |
+-------+------+
| BBB   | 88%  |
+-------+------+
1 row in set

+-------+------+
| units | ind2 |
+-------+------+
| AAA   | 59%  |
+-------+------+
1 row in set
18

use your imagination

how would you combine two identically structured result sets?

19

you are right… teacher :slight_smile: thanks so much …

mysql> SELECT
	t.units,
	t.ind1
FROM
	(
		SELECT
			MAX(ind1) AS max_ind1
		FROM
			tbl_max
	) AS m
INNER JOIN tbl_max AS t ON t.ind1 = m.max_ind1
UNION ALL
	SELECT
		t.units,
		t.ind2
	FROM
		(
			SELECT
				MAX(ind2) AS max_ind2
			FROM
				tbl_max
		) AS m
	INNER JOIN tbl_max AS t ON t.ind2 = m.max_ind2;
+-------+------+
| units | ind1 |
+-------+------+
| BBB   | 88%  |
| AAA   | 59%  |
+-------+------+
2 rows in set