I want to select records from a table which has a column with value -1 and +1
The ratio of -1 values to +1 values is about %17
But, I want to select records with balanced rate of -1 and +1 values.
For example I want to have select result with about %30 with -1 value and %70 with +1 value.
For example I have the following data set:
A B -1
A C -1
C Y +1
C A +1
C B +1
B C -1
A D +1
A F +1
D F +1
C F +1
since you didn't state the rules required clearly i could only guess this is what you want.
declare @percentage_1 int, @percentage_2 int, @recs int, @rn int, @rp int select @percentage_1 = 50, @percentage_2 = 50, @recs = 4 select @rn = @percentage_1 * @recs / 100, @rp = @percentage_2 * @recs / 100 ; with cte as ( select *, r = row_number() over (partition by value order by col1, col2) from your_table ) select * from cte where (value = -1 and r <= @rn) or (value = +1 and r <= @rp) order by value, col1, col2