title: SQL value at min query without join
body: <p>Quite often I have to solve the following problem. Suppose I have 3 columns: Col A, Col B, Col C.</p>
  
  <p>Col A contain some date which I need group on. Col B contains value for which I need to find minimum for each group in Col A. And Col C contains data for which I would like to find where minimum occurred, that is pull value from Col C corresponding to min in Col B. </p>
  
  <p>I commonly solve this problem, but writing JOIN statement. Does anybody know better solution, without JOIN? The problem seem so common to me that I would image it would make sense to create dedicated SQL command. It is like finding value of the function at a point of minimum (from math point of view)</p>
  
  <p>Regards,</p>
  
  <p>UPDATE.</p>
  
  <p>I apologize for not posting an example of what I mean. Here is my table:</p>
  
  <pre><code>Location            Quantity    Street
  New York            2            Broad
  New York            3            Main
  Pittsburgh          1            Grove
  Pittsburgh          5            School
  Austin              7            Hayes
  Austin              2            Barn
  </code></pre>
  
  <p>I would like to group by "Location" and choose min in "Quantity" for each group. Finally I would like to find "Street" corresponding to each min value. </p>
  
  <p>Here is how my final output should look like:</p>
  
  <pre><code> Location           Quantity    Street
   Austin             2           Barn
   New York           2           Broad
   Pittsburgh         1           Grove
  </code></pre>
  
  <p>And here is how I accomplish it ( I believe it is too long and there should be dedicated SQL function for it)</p>
  
  <pre><code>SELECT TheFunction.Location, Quantity, Street  
  FROM [AnalysisDatabase].[dbo].[ValueAtMin] As TheFunction
  
  inner join 
  
  (SELECT [Location], Min([Quantity]) as MinValue
  FROM [AnalysisDatabase].[dbo].[ValueAtMin] Group by [Location]) as XValue
  
  on TheFunction.Location = XValue.Location and XValue.MinValue = TheFunction.Quantity
  </code></pre>