Databases
StackOverflow2013
Postsdbo
POSelecting the rows with min value for a field, where the rest of fields differ
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelecting the rows with min value for a field, where the rest of fields differ
    primarykey
    data
    text
    <p>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. </p> <p>The table that looks like this:</p> <pre><code>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 </code></pre> <p>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.</p> <p>The result from the above sample data should be this:</p> <pre><code>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 </code></pre> <p>Thanks</p> <p>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:</p> <pre><code>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 </code></pre> <p>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?</p> <p>Thanks again!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTheRealPapa
    1. USTheRealPapa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COSorry, I dint get, you saying tat Dist is a unique key, then how could it repeat, there is IM in 3 records
      singulars
      1. POSelecting the rows with min value for a field, where the rest of fields differ
      1. USMd. Parvez Alam
    2. COHi Md. Parvez Alam, the Dist_Part_Num is unique key (it does not repeat). Juergen d's answer seems close as I think he mistook Dist_Part_Num with Ven_Part_Num. Once I make this change I end up close to the result. Only issue is where two Dist rows (different Dist) contain the same Ven_Part_Num at the same MIN price... I get the two rows, where I really want any of the two, so only one row extracted. Do you know how I can filter to one row?
      singulars
      1. POSelecting the rows with min value for a field, where the rest of fields differ
      1. USTheRealPapa
    3. COTry distinct keyword in inner query
      singulars
      1. POSelecting the rows with min value for a field, where the rest of fields differ
      1. USMd. Parvez Alam
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload