Minimum value for each group

Transact-SQL
1

Hi everyone
I am trying to get the min value for each id and label.
batches

As seen is the table above, I want to create a new column which displays the minimum value for each ID

The value column is always either 0 or 1 , so even if one value is 0 for each ID , then all the values for that id should be 0.

Thanks in advance for the help!!!

2

You can do that with ROW_NUMBER OVER()

Here's my template for that construction

SELECT	
	M.MainCol1
	, M.MainCol2
	, C.ChildCol1
	, C.ChildCol2
--	, C.ChildOtherCol1
--	, C.ChildOtherCol2
--	, C_RowNumber	-- Not normally required in the results
FROM	dbo.MyMainTable AS M
	JOIN
	(
		SELECT	[C_RowNumber] = ROW_NUMBER()
				OVER
				(
					PARTITION BY C.MatchColumn1
						, C.MatchColumn2
					ORDER BY 
					-- ** NOTE: This Sort Order must present the Limit Row(s) to RETAIN FIRST
						C.SortColumn1
						, C.SortColumn2
--						, CP.PartitionSortColumn1
--						, CP.PartitionSortColumn2
				)

			, [C_GroupRowCount] = COUNT(*)
				OVER
				(
					PARTITION BY C.MatchColumn1
						, C.MatchColumn2
				)
			, [C_GroupMaxDate] = MAX(C.ChildDateColumn)
				OVER
				(
					PARTITION BY C.MatchColumn1
						, C.MatchColumn2
				)
			, [C_TotalRowCount] = COUNT(*) OVER()
			, [C_TotalRowNumber] = ROW_NUMBER()
				OVER
				(
					ORDER BY 
						C.SortColumn1
						, C.SortColumn2
--						, CP.PartitionSortColumn1
--						, CP.PartitionSortColumn2
				)

			, C.MatchColumn1
			, C.MatchColumn2
			, C.ChildCol1
			, C.ChildCol2
-- 			, C.ChildSortCol1
-- 			, C.ChildSortCol1
--			, CO.ChildOtherCol1
--			, CO.ChildOtherCol2
		FROM	dbo.MyChildTable AS C
--			JOIN ChildOtherTableRequiredForTheSELECT AS CO
--			JOIN ChildOtherTableOnlyUsedForPartition_OrderBy AS CP
		WHERE	C.FilterColumn1 LIKE 'SomeValue%'	-- Optional child-row filter
	) AS C
		-- Usually the MatchColumns are the same as the PKey columns
		 ON C.MatchColumn1 = M.MatchColumn1
		AND C.MatchColumn2 = M.MatchColumn2
WHERE	C_RowNumber <= 5	-- Optional Child "Limit Rows"
ORDER BY M.MatchColumn1
	, M.MatchColumn2
	, C.ChildSortCol1
	, C.ChildSortCol1
3

I can't test this since you didn't provide useable data, but I think it gives you what you want:

SELECT Id, Label, Value, MIN(Value) OVER(PARTITION BY Id) AS Min_Value
FROM dbo.table_name