Table A of customer data
ID - Name - Age - etc
1 - Joe - 24
2 - Bloggs - 46
Table B is a temp table of random numbers
ID - RandomNumber
1 - 68
2 - 46
3 - 49
4 - 24
I need to select from table A
SELECT * FROM tableA a WHERE a.age = [randomNumber from tableB]
The where clause needs to look into tableB. Looks at first random number - 68, does this exist in Age column of tableA, no, so move to next random number. does 46 exist in tableB, yes, it then returns 2 - Bloggs - 46, and inserts that row into another table.
the process starts again but from where is left off - does 49 exist in tableA, no, so next, does 24 exist in tableA, yes, copy that row into other table.
i have a list of 150 students, and want to randomly select 30 by age.
I could use RAND() function but number will change every time, so id rather place random numbers in a table first, and then look up.
That's a really bad way to do that. O(n^2) runtime. Use a tally table.
Something like this:
declare @A table(id int, name varchar(20), age int)
--ID - Name - Age - etc
insert into @A (id, name, age) values
(1, 'Joe', 24),
(2, 'Bloggs', 46);
with n1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) _(_)),
n2(n) as (select 1 from n1, n1 _),
N(n) as (select 1 from n2, n2 _),
rands(n) as (select top 120 abs(checksum(newid()))%120 from N)
select * from @A
where age = any(select n from N)
Let SQL do the iteration. Think set-based logic not procedural, iterative process
Good to hear, we are all learning all the time and there is such a lot to consider and different ways of doing things, its good though that you can also be pulled when something can be done better and you have to take that on board