Selecting results from subquery

I have a table "Article" [product in ENG] which contains as attributes (numArticle [PK], poids [weight in English], des_ [Designation], couleur [color]) and I want to select the designation of the products with highest weight from each color.

I tried this

SELECT des_
FROM Article
WHERE poids = ANY (SELECT  MAX(poids) 'poidse'
                   FROM Article
                   GROUP BY couleur);

but didn't work and I didn't know why at first but figured it out later.

Simply put I want to assign to each weight of a product in subquery it's designation.

The subquery returns the highest weight of each color but doesn't give the designation since I can't use select designation without including it in GROUP BY Clause because if I did that its gives unwanted result.

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.