random name generator query

Question

random name generator query

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
January 9, 2008 at 4:21 am

#95779

Hi
I wonder if someone would be good enought to help/point me in the right direction/tell me how to do this
I have a table with the columns first_name, last_name thats consists of 5000 odd (you guessed it) first and last names.
How do I randomly select a different combination of names so I mix and match the first and last names to get a different combination each time, I might require 50 one time, hundred another etc
many , many thanks
~si
September 2, 2023 at 5:14 am

This was removed by the editor as SPAM
September 2, 2023 at 5:16 am

This was removed by the editor as SPAM
September 2, 2023 at 5:17 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's a similar thread where someone needed to generate the same kind of random sample...read matt Millers example here: http://www.sqlservercentral.com/Forums/Topic427826-338-2.aspx you'd change just the portion that did TOP 1000000 to TOP 20 or however many nam es you needed.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Simplest method I know of is to cross join the table to itself for distinct first and last name and order by newid() while using TOP n to limit the numbers.

Select TOP 5000 DISTINCT A.First_Name, B.Last_Name From myTable A CROSS JOIN myTable B ORDER BY NEWID()

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 3

I try

Select TOP 5000 distinct A.First_Name, B.Last_Name

From firstandlast A CROSS JOIN firstandlast B

ORDER BY NEWID()

and

Server: Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'distinct'.

kind regards

si

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 4

select a.first_name, b.last_name

from table a, table b

order by new_id()

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Oops, order of operation thing here

Select distinct TOP 5000 A.First_Name, B.Last_Name

From firstandlast A CROSS JOIN firstandlast B

ORDER BY NEWID()

However I think it limites to 5000 first then distincts which may drop a bunch more. So maybe

Select TOP 5000 A.First_Name, B.Last_Name

From firstandlast A CROSS JOIN firstandlast B

GROUP BY A.First_Name, B.Last_Name

ORDER BY NEWID()

Or worse case

SELECT TOP 5000 * FROM (Select distinct A.First_Name, B.Last_Name

From firstandlast A CROSS JOIN firstandlast B) tblSub

ORDER BY NEWID()

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 6

thank you one and all

I shall have a play

one final question ,w aht would be the preferred approach, a database table idea as I set out to do when starting this thread or an on the fly generation of names as suggested in the similar thread

take care all

si

Antares686 SSC Guru Points: 125444 More actions · Answer 7

It would all depend on your circumstances. If you need often then I would create a table with a bunch of names then pull from it using TOP n and ORDER BY NEWID(). If just occasionally then do as described here if you just want to save space or the need is very irregular.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

In your case - what's most efficient is going to be directly related to how big your firstandlast table is. If it happens to be 5000 records, the cross join (which will have to be processed in entirety to do the ORDER BY and TOP operation) would be 25Million records, which is going to hammer your temp database.

If it's a lot smaller - you shouldn't have too many issues either way.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?