5 Replies - 267 Views - Last Post: 25 January 2019 - 01:58 PM

#1 fearfulsc2   User is offline

  • D.I.C Regular

Reputation: 16
  • View blog
  • Posts: 279
  • Joined: 25-May 16

Randomly Select after a random selection

Posted 25 January 2019 - 01:24 PM

Hi everyone, I am trying to Randomly select x amount of items up until I reach a total of y amount of items.

What I'm doing right now is I'm randomly selecting a subset of items from different groups but they have a specified amount such as I want 2 random items from Group1 and 3 random items from Group2, etc..... until I reach a total of 20 selected items.

However, if Group1 only had 1 item, I will obviously be one item short and will have to select 1 more item from one of the other groups but that can be at random.

How would I be able to do that?

This is what I have been doing so far

with SOME_CTE as (
select 
	SomeNumber
	,AnotherNumber
	,SomeCost
	,c.GroupNumber
	,sr.Value GroupList
	,cast(sr.LowValue as int) GroupCount
from SomeTable c
join LookupTable sr on 
	(
		sr.SetName = 'GROUP' and
		',' +sr.Value + ',' like '%,' + c.GroupNumber+ ',%'
	)
where c.AsOfDate = '8-31-2020' -- these are dates I am using to select from range of dates
and DateLooked >= dbo.fn_date_month_start('8-31-2020')
and DateLooked <= '8-31-2020'
and SomeCost between 250000 and 2000000
),
Random_CTE as (
	select 
		*,
		abs(convert(bigint,convert(binary(8), newid()))) % 1000 / 1000.0 as SomeRandom
	from	
		SOME_CTE
),
RowNumber_CTE as (
	select top 999999
		*,
		row_number() over (partition by GroupList order by SomeRandom) RowNumber
	from	
		Random_CTE
	where 
		GroupList is not null
	order by
		GroupList, RowNumber
),
Finding_CTE as (
	select *,
	case
		when RowNumber <= GroupCount
		then null
		else 'Not Selected'
		end FindingName	
	from
	RowNumber_CTE
)



That's what I currently have so far.

So let's say I get a list of 32,
20 of them have to have a null value for finding name. However, if there are under 20, I need to randomly select from the 'Not Selected' items in the list and change them to null.

Any ideas?

Is This A Good Question/Topic? 0
  • +

Replies To: Randomly Select after a random selection

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: Randomly Select after a random selection

Posted 25 January 2019 - 01:39 PM

First question is does this have to happen in SQL? Seems a better job for C# .
Was This Post Helpful? 0
  • +
  • -

#3 fearfulsc2   User is offline

  • D.I.C Regular

Reputation: 16
  • View blog
  • Posts: 279
  • Joined: 25-May 16

Re: Randomly Select after a random selection

Posted 25 January 2019 - 01:44 PM

Yes, it has to be in SQL due to a certain job process we follow every night.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: Randomly Select after a random selection

Posted 25 January 2019 - 01:50 PM

A nightly job process that just crams X number of Y items together sans reason?

Now you have me intrigued. :^: I am having issues seeing it - what's the business case this is fulfilling?
Was This Post Helpful? 0
  • +
  • -

#5 fearfulsc2   User is offline

  • D.I.C Regular

Reputation: 16
  • View blog
  • Posts: 279
  • Joined: 25-May 16

Re: Randomly Select after a random selection

Posted 25 January 2019 - 01:56 PM

I can't go into much detail other than once a month this process will run and will select a sample size from a list of deals.
Those deals can come from different groups and we want a certain subset from each of those groups that will add up to 20 in total while the rest are not sampled.

There is no issue at all if we get the 20 in total from the groups but if one group falls short, I need to figure out a way to randomly select from the rest that were not sampled until the total adds up to 20.

And the purpose for all of this is for auditing/compliance purposes

So right now, I can randomly select the items and get what I want assuming I have enough data from the previous month.

The issue is if I needed 5 from one of the groups but that group only has 4. Now I have to figure out how to randomly select from the group of not sampled deals and put it into the group of sampled deals until the count is 20.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15314
  • View blog
  • Posts: 61,406
  • Joined: 12-June 08

Re: Randomly Select after a random selection

Posted 25 January 2019 - 01:58 PM

Aaaah.. okay.. auditing and compliance. I see the direction. Here I thought you were going to be listing out top 20 hit songs played in the departmental radio.

The most direct route would be to randomize the data into a temp table using 'order by new id', and use a CURSOR to iterate over the rows adding them to some collection until what ever quantity is needed is filled.

create table #foo (lval int, sval varchar(10))

insert into #foo (lval, sval) values (1, 'asdf')
insert into #foo (lval, sval) values (2, 'sghdfg')
insert into #foo (lval, sval) values (3, 'cvbn')
insert into #foo (lval, sval) values (4, '124f')

select *
from #foo
order by NEWID()



drop table #foo



lval        sval
----------- ----------
2           sghdfg
4           124f
1           asdf
3           cvbn

lval        sval
----------- ----------
2           sghdfg
1           asdf
3           cvbn
4           124f

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1