This is for SQL Server. Is there a way to get the minimum value of only a select group of columns based on the value of another column? In this example, I want to find the minimum transaction that each Name had and place that in the 'Minimum Transaction' column. I cannot hard code the value of 'Name'. I have tried a subquery, but I can't wrap my head around how the logic would work.

SELECT ID, Name, Transactions, Minimum Transaction =
   (
   SELECT MIN(Transactions) 
   FROM MyTable M
   JOIN MyTable M ON N.ID = M.ID
   )
FROM MyTable N
WHERE Name = 'Sarah' OR 'Sue' OR 'Joe' OR 'Tim'

Expected Outcome

CodePudding user response：

Use MIN(Transactions) OVER(PARTITION BY Name) :

SELECT ID, Name, Transactions, 
MIN(transactions) over (partition by Name) as "Minimum Transaction" from 
MyTable
order by id;

PARTITION BY as opposed to GROUP BY only affects the window function and doesn't affect the returned rows.

Fiddle