mysql – Get the field with the highest value grouped by codes

Question:

I have two tables related by materiaprimaID , the tables are as follows:

**Raw_matrial**
materiaprimaID (int)
codigo (int)

**composicion**
compuesto (varchat)
entryNumber (int)
porcentaje (int)
materiaprimaID (int)

I am trying to create a query in sql to get from all the codes the field with the highest percentage value.

A code contains several compounds with their percentage

Código | Porcentaje  | Compuesto
6014   | 0,084       | Crotonaldehyde
6014   | 0,022       | Hexanal, n
6014   | 98,810      | Furfural
6014   | 0,012       | Benzofuran
6021   | 99,595      | Iso Propyl Cinnamate
6021   | 0,305       | Ethyl Cinnamate

And I want to get all the code but I am only interested in the compound with the highest percentage, hoping to get:

Código | Porcentaje  | Compuesto
6014   | 98,810      | Furfural
6021   | 99,595      | Iso Propyl Cinnamate

I have a query, which resembles but does not give me the expected results:

SELECT
raw_material.codigo,
composicion.porcentaje,
composicion.compuesto
FROM
composicion
INNER JOIN raw_material ON raw_material.materiaprimaID = composicion.materiaprimaID
ORDER BY
composicion.porcentaje DESC

RESULT:

6021    99,595  Iso Propyl Cinnamate
6014    98,810  Furfural
6014    0,609   Methyl Furfural, 5-
6021    0,305   Ethyl Cinnamate
6014    0,141   Acetyl Furan, 2-
6014    0,084   Crotonaldehyde
6014    0,057   Guaiacol
6014    0,022   Hexanal, n

But I only want the Composite of each code with the highest percentage. And I can't do a LIMIT because the search can have hundreds of codes.

Thank you for your answer.

Thank you.

Answer:

What you are trying to achieve is something like this:

SELECT codigo,porcentage,compuesto FROM (
    SELECT materiaprimaID,
      MAX(porcentage) porcentage
      FROM composicion
      GROUP BY 1  
  ) c1 JOIN composicion USING(materiaprimaID,porcentage)
  JOIN raw_matrial USING(materiaprimaID);

For this test dataset:

CREATE TABLE raw_matrial(
  materiaprimaID int PRIMARY KEY,
  codigo int
  );
CREATE TABLE composicion(
  compuesto varchar(31),
  entryNumber int,
  porcentage float,
  materiaprimaID int,
  FOREIGN KEY(materiaprimaID) REFERENCES raw_matrial(materiaprimaID)
  );
INSERT INTO raw_matrial (materiaprimaID, codigo) VALUES
  (14,6014),(21,6021);
INSERT INTO composicion (materiaprimaID, porcentage, compuesto) VALUES
  (14,0.084,'Crotonaldehyde'),  
  (14,0.022,'Hexanal, n'),  
  (14,98.810,'Furfural'),   
  (14,0.012,'Benzofuran'),  
  (21,99.595,'Iso Propyl Cinnamate'),   
  (21,0.305,'Ethyl Cinnamate');

The result would be:

6014  98,81   Furfural
6021  99,595  Iso Propyl Cinn

Of each group, the one with the highest percentage.

I'll explain it a bit: first you look for the maximums of each Code (which has several compounds) and then you look for the compounds to which they correspond by combining the subquery and the table by two fields. It can't be done directly in MAX because SQL doesn't work like that.

The GROUP BY 1 is to group by the first field that appears after the SELECT , it is an abbreviated way of writing GROUP BY Código

The alias c1 , or any other, is mandatory when you use subqueries. It is a combination of a table and a subquery. Nesting maybe sounds more like loops to me …

Post Views: 2
Scroll to Top