Select Minimum Value From Group

[Guillermo_Lopez]

Hello All,

What I am trying to do here is select the smallest value from a group
of data. If i have something like this:

Item_ID SizeType LocCapacity
P1234 Type1 334
P1234 Type2 100
P5678 Type1 54
P5678 Type2 80

And the result i want is for each ItemID what is the Type with the
smallest capacity.

I have done it before about a year ago, and it worked great, except
that now i forgot how i did it, and i can't recover my resource. The
way i have it now which should work, takes too long to run my table of
13,000 records. And I remember there was another way to do this, but i
forgot how.

SELECT Q1.Item_ID, Q1.SizeType, Q1.LocCapacity
FROM Q209_2_All_Locations AS Q1
WHERE Q1.SizeType In (SELECT Top 1 Q2.SizeType, Q2.Item_ID FROM
Q209_2_All_Locations AS Q2 WHERE [Q2].[Item_ID]=[Q1].[Item_ID] ORDER
BY [Q2].[LocCapacity];

Thanks in Advanced

- GL

 

[Guillermo_Lopez]

Hello All,

What I am trying to do here is select the smallest value from a group
of data. If i have something like this:

Item_ID    SizeType   LocCapacity
P1234        Type1           334
P1234        Type2           100
P5678        Type1             54
P5678        Type2             80

And the result i want is for each ItemID what is the Type with the
smallest capacity.

I have done it before about a year ago, and it worked great, except
that now i forgot how i did it, and i can't recover my resource. The
way i have it now which should work, takes too long to run my table of
13,000 records. And I remember there was another way to do this, but i
forgot how.

SELECT Q1.Item_ID, Q1.SizeType, Q1.LocCapacity
FROM Q209_2_All_Locations AS Q1
WHERE Q1.SizeType In (SELECT Top 1 Q2.SizeType, Q2.Item_ID FROM
Q209_2_All_Locations AS Q2 WHERE [Q2].[Item_ID]=[Q1].[Item_ID] ORDER
BY [Q2].[LocCapacity];

Thanks in Advanced

- GL

Sorry, The SQL I included should be like this:

SELECT Q1.Item_ID, Q1.SizeType, Q1.LocCapacity
FROM Q209_2_All_Locations AS Q1
WHERE Q1.SizeType In (SELECT Top 1 Q2.SizeType FROM
Q209_2_All_Locations AS Q2 WHERE [Q2].[Item_ID]=[Q1].[Item_ID] ORDER
BY [Q2].[LocCapacity];

That was just a Typo. My question stands.

 

[Guillermo_Lopez]

Hello All,

What I am trying to do here is select the smallest value from a group
of data. If i have something like this:

Item_ID    SizeType   LocCapacity
P1234        Type1           334
P1234        Type2           100
P5678        Type1             54
P5678        Type2             80

And the result i want is for each ItemID what is the Type with the
smallest capacity.

I have done it before about a year ago, and it worked great, except
that now i forgot how i did it, and i can't recover my resource. The
way i have it now which should work, takes too long to run my table of
13,000 records. And I remember there was another way to do this, but i
forgot how.

SELECT Q1.Item_ID, Q1.SizeType, Q1.LocCapacity
FROM Q209_2_All_Locations AS Q1
WHERE Q1.SizeType In (SELECT Top 1 Q2.SizeType, Q2.Item_ID FROM
Q209_2_All_Locations AS Q2 WHERE [Q2].[Item_ID]=[Q1].[Item_ID] ORDER
BY [Q2].[LocCapacity];

Thanks in Advanced

- GL

Sorry, The SQL I included should be like this:

SELECT Q1.Item_ID, Q1.SizeType, Q1.LocCapacity
FROM Q209_2_All_Locations AS Q1
WHERE Q1.SizeType In (SELECT Top 1 Q2.SizeType FROM
Q209_2_All_Locations AS Q2 WHERE [Q2].[Item_ID]=[Q1].[Item_ID] ORDER
BY [Q2].[LocCapacity];

That was just a Typo. My question stands.- Hide quoted text -

- Show quoted text -

I have somewhat solved part of my Problem... But i still need a bit of
help. I finally remember how to do this sort of problem. Here is my
solution:

query 1: Q209_3
SELECT Q209_2_All_Locations.Item_ID,
Min(Q209_2_All_Locations.LocCapacity) AS MinOfLocCapacity
FROM Q209_2_All_Locations
GROUP BY Q209_2_All_Locations.Item_ID;

And query 2:
SELECT Q209_2.Item_ID, Q209_2.SizeType, Q209_2.LocCapacity
FROM Q209_2_All_Locations AS Q209_2 INNER JOIN Q209_3 AS Q209_3 ON
(Q209_2.LocCapacity = Q209_3.MinOfLocCapacity) AND (Q209_2.Item_ID =
Q209_3.Item_ID)
ORDER BY Q209_2.Item_ID;

It works great and fast. Except that there are some items that have
the same Capacity on two or more SizeType. This causes the data to
duplicate. And in my case I prefer to use the smaller SizeType (ie.
Type1 has a cubic volume of 8.12 and Type2 of 8.3) I'd use Type1, even
if both can hold a capacity of 8 cases.

- GL