CODEWITHSUNDEEP
ms-access
PVic
PVic

Reputation: 459

Sub Query - Show Column B when Column C is Max of Group in Col A

I have 3 columns (ID, Month, & Qty). ID column has multiple groups in it, and the Month goes from Jan-Dec and cycles through if there's enough values. I'm trying to find the Max(Qty) of each ID group, and show the Month that Max occurred. I'm trying to practice using Subqueries, so below is what I have so far...

SELECT t.ID, t.Month, t.Qty

FROM [Monthly Qty] t

WHERE t.Qty = (SELECT MAX(Qty) FROM [Monthly Production] m WHERE t.ID=m.ID);

Example of what I'm looking for is below. Starting with...

ID Month Qty

A Jan 5

A Feb 8

B Sep 10

B Oct 2

I would want my results to show...

ID Month Qty

A Feb 8

B Sep 10

Any help would be appreciated especially if it can be with a subquery, but if it's too much of a hastle I'll take whatever ya'll can give haha.

Upvotes: 1

Views: 84

Answers (1)

user6788933
user6788933

Reputation: 287

I was faced with the same problem before and could not find a simple /fast solution as one would expect.

You can use select top 1 .... Order by Qt desc for each group.

SELECT JustATableWithEachGroup.id,
  (SELECT TOP 1 Mon FROM MonProd 
    WHERE id=JustATableWithEachGroup.id
    ORDER BY MonProd.Qty DESC) AS TopMonth,
  (SELECT TOP 1 Qty FROM MonProd
    WHERE id=JustATableWithEachGroup.id
    ORDER BY MonProd.Qty DESC) AS TopQuant
FROM 
  (SELECT DISTINCT MonProd.ID
  FROM MonProd)  AS JustATableWithEachGroup;

Upvotes: 1

Related Questions