how to generate random data

Question

Post reply

how to generate random data

la-461637

SSC Enthusiast

Points: 134
More actions
July 7, 2007 at 5:06 am

#196839

Dear All,
I have some problems with the assignment from my professor.
Suppose there are two tables storing first names and last names, how to write stored procedures to get a random first name + random last name for Males or Female. like you got a list of all M & F first name and last name, then you try to match first name with any other last name (I believe this is random).
I am totally struck and no idea how to deal with it, in fact I am just a beginning in SQL server, and I know there is a stored procedure to generate random number, however, I can't see the use of it here,,,,

Could anyone help me out ? thanks very much !!!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

SELECT TOP 100 LastName, FirstName

FROM (

SELECT LastName.LastName,

FirstName.FirstName

FROM Table1 AS LastName

CROSS JOIN Table1 AS FirstName

WHERE LastName.Gender = 'M' AND FirstName.Gender = 'M'

UNION ALL

SELECT LastName.LastName,

FirstName.FirstName

FROM Table1 AS LastName

CROSS JOIN Table1 AS FirstName

WHERE LastName.Gender = 'F' AND FirstName.Gender = 'F'

) AS d

ORDER BY NEWID()

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

A bit confused by your original post... what do the two tables contain? One table for FirstNames and one table for LastNames? Or one table for Male and another for Female?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

la-461637 SSC Enthusiast Points: 134 More actions · Answer 3

thanks for the reply!!

sorry for the confusion, there are two tables, one is for first names with columns of names and gender, and the other table is for last names.and only with one column..

la-461637 SSC Enthusiast Points: 134 More actions · Answer 4

Hello! Peter, thanks for your reply!

But the problem is how can I run the script each time and just get a random result? for example, I run the script with the attempt to get just one random female name ?

thanks again!

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 5

Replace TOP 100 with TOP 1.

N 56°04'39.16"
E 12°55'05.25"

la-461637 SSC Enthusiast Points: 134 More actions · Answer 6

thanks !! sorry I was not reading carefully enough !

you are really a life saver!