Retrieving randomized name for each alpha letter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Retrieving randomized name for each alpha letter

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2009-12-14 : 10:58:35

I'd like a SELECT statement to retrieve a single randomized name for each of the letters of the alphabet. I can do it for the entire group of names, but I haven't figured out how one query can do this for only a single randomized name for each of the letters of the alphabet.

Here's what I have so far:
Select custnumber, Newid() as RandomOrder, left(lastname,1) as Initial from customers order by randomOrder

Any help would be appreciated.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-14 : 11:37:39

Can you give any examples?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

RichardSteele
Posting Yak Master

160 Posts

Posted - 2009-12-14 : 12:06:31

I want it to retrieve
the letter and one randomized lastname that begins with that letter.

A - Appel (Appel is a randomized name from many other lastnames that begin with the letter A

B - Buchannon (Buchannon is a randomized name from many other lastnames that begin with the letter B

etc.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-14 : 12:21:17

This?

declare @Names table (Name varchar(20))
insert @Names
          select 'Appel'
union all select 'Adams'
union all select 'Adanet'
union all select 'Adler'
union all select 'Ahmad'
union all select 'Ahmed'
union all select 'Alexander'
union all select 'Buchannon'
union all select 'Baker'
union all select 'Banks'
union all select 'Barker'
union all select 'Barry'
union all select 'Bass'
union all select 'Begum'

select Name from (select *, row_number() over (partition by left(Name, 1) order by newid()) as Row from @Names) a where Row = 1

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-12-14 : 12:26:36

Are you looking for the "same" random name for each surname? or total Randomised names?

Get 1 random name for each alphabet letter and join that outGut to your coredataset?

there have been previous questions here recently on getting "1 max/top" record for a grouping (in this case a alphabet letter). a variation on that should work.

below would be close, but i don't think good enough....suspect it won't generate one per "Alphabet" letter....unless you input same into some table with a unique index?

select top 26
left(b.surname,1), b.surname
from randomnameset b
order by newid()

RichardSteele
Posting Yak Master

160 Posts

Posted - 2009-12-14 : 12:45:11

This is close, but I'm getting an error that "OVER" is not supported in SQLSERVER 2005.
R

quote:

Originally posted by RyanRandall

This?

declare @Names table (Name varchar(20))
insert @Names
          select 'Appel'
union all select 'Adams'
union all select 'Adanet'
union all select 'Adler'
union all select 'Ahmad'
union all select 'Ahmed'
union all select 'Alexander'
union all select 'Buchannon'
union all select 'Baker'
union all select 'Banks'
union all select 'Barker'
union all select 'Barry'
union all select 'Bass'
union all select 'Begum'

select Name from (select *, row_number() over (partition by left(Name, 1) order by newid()) as Row from @Names) a where Row = 1

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

RichardSteele
Posting Yak Master

160 Posts

Posted - 2009-12-14 : 13:16:15

Wow, that is it! Even though I was getting the error message in Studio Express it worked!

Now, is this a random order of the entire group of 26 alpha names or is it a random order of each name within each letter? The reason I ask is that some of the names appear to repeat quite often when running the query multiple times, while if they were randomizing within each letter I don't think I would see the name show up so often.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 05:23:07

It's a random order of each name within each letter.

It's quite common for people to see patterns in randomness. Here's some reading if you're interested: http://www.google.com/search?q=patterns+in+randomness

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources