You can use ROW_NUMBER to order the records for each color starting with the one with highest poids. Then, SELECT only the first ones from each color using TOP 1 WITH TIES :

SELECT TOP 1 WITH TIES 
        numArticle,
        poids,
        des_,
        couleur
FROM Article
ORDER BY ROW_NUMBER() OVER (PARTITION BY couleur ORDER BY poids DESC)

My understanding of the question you raised is as follows:

• you want to group by color and MAX in weight at the same time,but you can't do like this select des_,max(weight),color from [table] gourp by color
• so you need construct a subquery like this SELECT couleur, MAX(poids) AS max_poids FROM Article GROUP BY couleur to connect weight and color
• You can use a join to solve this problem. Specifically, you can use an inner join to join the Article table with the subquery that groups by maximum weight and color, and retrieve the desired result by using the maximum weight of each color from the subquery and the corresponding designation from the article table.

Solution

SELECT Article.des_
FROM Article
INNER JOIN (
    SELECT couleur, MAX(poids) AS max_poids
    FROM Article
    GROUP BY couleur
) AS max_poids_coul
ON Article.couleur = max_poids_coul.couleur AND Article.poids = max_poids_coul.max_poids;

PS:

In this query, the subquery uses GROUP BY to retrieve the maximum weight for each color and adds an alias to the maximum weight column using the AS keyword. Then, by using an INNER JOIN to connect the subquery result with the Article table, the desired Designation is retrieved by matching the color and weight.