Looping function

Transact-SQL
1

Hi,

I have two tables:

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.

any help is much appreciated.

2

You just need a where exists, something like:

SELECT * INTO mytable FROM #tableA a
WHERE EXISTS
  (SELECT RandomNumber b
    FROM #tableB b
    WHERE a.id = b.id
    );
3

I've tried that but that will always search for first existence.
I want something that will iterate through tableB to match with tableA

4

Take a look here:
https://support.microsoft.com/en-us/kb/111401

5

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

6

Both procedures worked. I understood what @sz1 suggested better than @gbritton, but @gbritton has raised a good point.

Thanks both

7

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 :slight_smile:

8

Gosh no. No iteration is required here.

9

Is that just a manifestation of what you think is necessary to select 30 random students? Wouldn't it all be easier just to do the following?

 SELECT TOP 30 *
   FROM dbo.TableA
  ORDER BY NEWID()
;

Note that GUIDs produced by NEWID() are "Type 4" GUIDs which are just really big random numbers.