Selecting results from subquery

Question

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.

Please provide sample data and desired results to clarify your question. — Dale K, Apr 11 '23 at 07:55

score 2 · Answer 1 · answered Apr 11 '23 at 06:13

2

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)

answered Apr 11 '23 at 06:13

gotqn

42,737
46
157
243

2

Sql table consists of rows. Some Sql systems also have a record construct besides rows. Although there's no room for confusion in the case above still I would prefer using rows for rows. – Serg Apr 11 '23 at 06:47
2

Agree. I use `records` sometimes when I am trying to explain stuff as I believe it's easier for people to imagine they have `records` (data) for something. – gotqn Apr 11 '23 at 06:51

innovationLou · Answer 2 · 2023-04-11T06:43:41.527

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.

Selecting results from subquery

2 Answers2

Solution

PS: