CODEWITHSUNDEEP
sqlms-access
TheRealPapa
TheRealPapa

Reputation: 4539

Selecting the rows with min value for a field, where the rest of fields differ

I am trying to write SQL (Access 2010) to select parts which have a minimum price from a table where the parts can repeat, as some of the other fields are different.

The table that looks like this:

Dist Part Num  Ven Part Num     Dist  Desc                Price
DD7777QED      7777QED          DD    Product A              10
IM7777QED      7777QED          IM    This is Product A      12
SY7777QED      7777QED          SY    Product A Desc         15
DD8888QED      8888QED          DD    Product B              15
IM8888QED      8888QED          IM    This is Product B      10
SY8888QED      8888QED          SY    Product B Desc         12
IM999ABC       999ABC           IM    Product C Desc         15

I am trying to extract all details for each row that has the min price for that Ven Part Num that repeats. In essence all details for the supplier's row that has the cheapest price for that Vendor Part Number.

The result from the above sample data should be this:

Dist Part Num  Ven Part Num     Dist   Desc               Price
DD7777QED      7777QED          DD     Product A             10
IM8888QED      8888QED          IM     This is Product A     10
IM999ABC       999ABC           IM     Product A Desc        15

Thanks

EDIT: Thank you jurgen d for your answer, although I think you meant to use Ven Part Num (instead of Dist Part Num). I have ammended to this query now which almost works to what I want:

SELECT T1.* 
FROM My_Table T1
INNER JOIN
(
   SELECT [Ven Part Num], MIN(Price) AS MPrice
   FROM My_Table
   GROUP BY [Ven Part Num]
) T2 ON T1.[Ven Part Num] = T2.[Ven Part Num] AND T1.Price = T2.MPrice

Challenge now is that if two Dist have the same MIN price for the same Ven Part Num, then the resulting extract contains 2 rows for that Ven Part Num, but I want just one, either will do. I tried TOP 1 but it runs and brings up only one row as result of the whole query. I have 40K rows I am expecting! How do I extract only one of these two rows in the final report?

Thanks again!

Upvotes: 0

Views: 675

Answers (1)

juergen d
juergen d

Reputation: 204756

select t1.* 
from your_table t1
inner join 
(
   select [Dist Part Num], min(price) as mprice
   from your_table
   group by [Dist Part Num]
) t2 on t1.[Dist Part Num] = t2.[Dist Part Num] and t1.price = t2.mprice

Upvotes: 1

Related Questions