[SOLVED] How to Find Max Value for each criteria in a Table View

How to Find Max Value for each criteria in a Table View

I have database name "spkfu" and it has four table for my final project.

first table is "Alternative"

id_alternative	name
1	Alex
2	Felix

second table is "Criteria" This table contains the weight of the criteria that have been determined by the expert

id_criteria	name_criteria	weight_value1	weight_value2	weight_value3
1	Job	0.40	0.60	0.80
2	School	0.75	0.85	0.95
3	Income	0.50	0.75	1.00

third table is "Intervals" this table has the value of each criterion

id_interval	id_criteria	value_criteria	value_interval	fuzzy_number1	fuzzy_number2	fuzzy_number3
1	1	1	low	0.00	0.25	0.50
2	1	2	med	0.25	0.50	0.75
3	1	3	high	0.50	0.75	1.00
4	2	1	low	0.00	0.25	0.50
5	2	2	med	0.25	0.50	0.75
6	2	3	high	0.50	0.75	1.00
7	3	1	low	0.00	0.25	0.50
8	3	2	med	0.25	0.50	0.75
9	3	3	high	0.50	0.75	1.00

fourth table is "Technical Aspect" This table has the value of each alternative and the criteria that have been filled in

id_tehnical_aspect	id_alternative	id_criteria	value_criteria
1	1	1	1
2	1	2	1
3	1	3	3
4	2	1	3
5	2	2	2
6	2	3	1

My Table view "v_datanilaikriteria" this table is the full version of the assessment aspect table

id_alternative	id_criteria	value_criteria	fuzzy_number1	fuzzy_number2	fuzzy_number3
1	1	1	0.00	0.25	0.50
1	2	1	0.00	0.25	0.50
1	3	3	0.50	0.75	1.00
2	1	3	0.50	0.75	1.00
2	2	2	0.25	0.50	0.75
2	3	1	0.00	0.25	0.50

with this code on view table

select `spkfu`.`alternative`.`id_alternative` AS `id_alternative`,`spkfu`.`criteria`.`id_criteria`     
AS `id_criteria`,`spkfu`.`technical_aspect`.`value_criteria`
AS `value_criteria`,`spkfu`.`intervals`.`fuzzy_number1` 
AS `fuzzy_number1`,`spkfu`.`intervals`.`fuzzy_number2` 
AS `fuzzy_number2`,`spkfu`.`intervals`.`fuzzy_number3` 
AS `fuzzy_number3` from (((`spkfu`.`technical_aspect` join `spkfu`.`alternative` on(`spkfu`.`alternative`.`id_alternative` = `spkfu`.`tehcnical_aspect`.`id_alternative`))
join `spkfu`.`criteria` on(`spkfu`.`criteria`.`id_criteria` = `spkfu`.`tehcnical_aspect`.`id_criteria`))
join `spkfu`.`intervals` on(`spkfu`.`intervals`.`id_criteria` = `spkfu`.`technical_aspect`.`id_criteria` and `spkfu`.`intervals`.`value_criteria` = `spkfu`.`technical_aspect`.`value_criteria`))

how do I want to find the maximum value (fuzzy_number3) for each criterion from the many alternatives, I need a result like this :

id_kriteria	max_number
1	1.00
2	0.75
3	1.00

I've tried to find the maximum value but can only 1 criteria per table with this code

select max(`spkfu`.`intervals`.`fuzzy_number3`) AS `max_k1`,`spkfu`.`criteria`.`id_criteria` AS 
`id_criteria` from (((`spkfu`.`technical_aspect` join `spkfu`.`alternative` 
on(`spkfu`.`alternative`.`id_alternative` = `spkfu`.`technical_aspect`.`id_alternative`)) join 
`spkfu`.`criteria` on(`spkfu`.`criteria`.`id_criteria` = `spkfu`.`technical_aspect`.`id_criteria`)) 
join `spkfu`.`intervals` on(`spkfu`.`intervals`.`id_criteria` = 
`spkfu`.`technical_aspect`.`id_criteria` and `spkfu`.`intervals`.`value_criteria` = 
`spkfu`.`technical_aspect`.`value_criteria`)) where `spkfu`.`criteria`.`id_criteria` = '1'

the result

max_k1	id_criteria
1.00	1

Solution

Remove the where condition and group by id.criteria

    select max(`intervals`.`fuzzy_number3`) AS `max_k1`,
           `criteria`.`id_criteria` AS `id_criteria` 
    from (((`technical_aspect` 
         join `alternative` on(`alternative`.`id_alternative` = `technical_aspect`.`id_alternative`)) 
         join `criteria` on (`criteria`.`id_criteria` = `technical_aspect`.`id_criteria`)) 
         join `intervals` on (`intervals`.`id_criteria` = 
    `technical_aspect`.`id_criteria` and `intervals`.`value_criteria` = 
    `technical_aspect`.`value_criteria`))  
   group by id_criteria;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/36