SQL Challenge? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Challenge?

Author

Topic

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-19 : 14:31:09

Check out this thread:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=30677

I think it's an interesting SQL problem. So, if we have:

create table People(Person varchar(20) primary key)

insert into People
select 'Ed' union
select 'Pete' union
select 'Mitch' union
select 'Lewy' union
select 'Paul' union
select 'Mary' union
select 'Lisa' union
select 'Tom'

how do we produce a nice random result with each name assigned to exactly 1 other name? And no one can be assigned to themselves.

Sounds like an interesting SQL challenge to me.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-19 : 14:44:45

Can we use temp tables?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-19 : 14:47:50

sure ... any solution is fine ... let's just see who can come up with the most "elegant" solution.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-19 : 15:10:13

Kinda like a sledger hammer...EDIT:

That doesn't work..

Hey no Cursor...

create table People(Person varchar(20) primary key)

insert into People
select 'Ed' union
select 'Pete' union
select 'Mitch' union
select 'Lewy' union
select 'Paul' union
select 'Mary' union
select 'Lisa' union
select 'Tom'
GO

CREATE TABLE #p1 (col1 int IDENTITY(1,1), Person varchar(20))
INSERT INTO #p1 (Person) SELECT Person FROM People ORDER BY NEWID()

CREATE TABLE #p2 (col1 int IDENTITY(1,1), Person varchar(20))
INSERT INTO #p2 (Person) SELECT Person FROM People ORDER BY NEWID()

CREATE TABLE #p3 (col1 int IDENTITY(1,1), Person varchar(20))
INSERT INTO #p3 (Person)
SELECT p1.Person
FROM #p1 p1
INNER JOIN #p2 p2 ON p1.Col1 = p2.Col1
WHERE p1.Person = p2.Person
ORDER BY p1.Person ASC

CREATE TABLE #p4 (col1 int IDENTITY(1,1), Person varchar(20))
INSERT INTO #p4 (Person)
SELECT p2.Person
FROM #p1 p1
INNER JOIN #p2 p2 ON p1.Col1 = p2.Col1
WHERE p1.Person = p2.Person
ORDER BY p2.Person DESC

SELECT p1.Person, p2.Person
FROM #p1 p1
INNER JOIN #p2 p2 ON p1.Col1 = p2.Col1
WHERE p1.Person <> p2.Person
UNION ALL
SELECT p3.Person, p4.Person
FROM #p3 p3
INNER JOIN #p4 p4 ON p3.Col1 = p4.Col1
WHERE p3.Person = p4.Person
GO

DROP TABLE #p1
DROP TABLE #p2
DROP TABLE #p3
DROP TABLE #p4
DROP TABLE Person
GO

Brett

8-)

The Enigma
Posting Yak Master

179 Posts

Posted - 2003-11-19 : 15:29:56

Brett ...
some problem with the code ..
I havent anaalyzed whats cousing it .. but sometimes you get only 6 records ...

Attitude is everything

{The Enigma}

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-19 : 15:48:38

My sweety kinky matches (ahhh... I'm not sure in the req of the subject):

select q.person,
(select min(person) from people p where p.person>q.person)
from
(select person from people p where
(select count(*) from people t where t.person>=p.person)%2=0) q

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-19 : 15:51:45

My resultset:


person                                    
-------------------- -------------------- 
Ed                   Lewy
Lisa                 Mary
Mitch                Paul
Pete                 Tom

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 16:01:13


CREATE TABLE #GiftMatch (PersonA varchar(20),PersonB varchar(20))

DECLARE @GiftCount tinyint,
	@Person varchar(20),
	@LastPerson varchar(20)

SET @GiftCount = 0
SET @LastPerson = ''

DECLARE ElegantCursor CURSOR FOR
SELECT Person 
FROM People
ORDER BY newid()

OPEN ElegantCursor 

FETCH NEXT FROM ElegantCursor 
INTO @Person
WHILE @@FETCH_STATUS = 0
BEGIN
	If ( @GiftCount%2 = 1 )
	BEGIN
		UPDATE #GiftMatch SET PersonB = @Person WHERE PersonA = @LastPerson
	END		
	ELSE
	BEGIN
		INSERT INTO #GiftMatch (PersonA) SELECT @Person
	END		

  	SET @GiftCount = @GiftCount + 1
	SET @LastPerson = @Person

	FETCH NEXT FROM ElegantCursor 
	INTO @Person
	
END

CLOSE ElegantCursor 
DEALLOCATE ElegantCursor 
GO

SELECT * FROM #GiftMatch
DROP TABLE #GiftMatch

Some bonus points for my naming convention???

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-19 : 16:17:07

LOL

ElegantCursor...

Certainly accomodates the randomness, and you won't have anyone assigned to themselves...

Don't forget SET NOCOUNT ON

Brett

8-)

The Enigma
Posting Yak Master

179 Posts

Posted - 2003-11-19 : 16:40:04

Stoad , that query does not get a random result ... As of mine ... still working on it

Attitude is everything

{The Enigma}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-19 : 16:44:36

quote:
Originally posted by ehorn

Some bonus points for my naming convention???

Sure, you get 2 points for naming convention, but -3 for using a cursor.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-19 : 17:09:01

Actually, i was thinking a resultset with 8 entries -- 1 per name -- each matched with another name.

So you are not really "pairing" people up, but rather assigning 1 to "do something" for the other, if that makes more sense. The original problem was, to find the person that each person needs to get a gift. So the final result would have 8 rows, since the table has 8 names ...

I will try to work on it myself tonight !!

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-19 : 17:25:37

Holy cross join Jeff! Can't you make it any tougher?

How about a list of 8 with no gift receiver giving a gift back to the giver?

e.g. this is wrong:

Ed Lisa
Lisa Ed
Mitch Paul
Pete Tom
Lewy Mitch
Mary Lewy
Paul Mary
Tom Pete

This set is wrong because Ed gives a gift to Lisa who gives a gift back to Ed.

While this is a tighter requirement, it may not matter. Maybe this requirement creates a second problem?

Sam

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-19 : 17:33:29

[code]
CREATE VIEW FirstHalf
AS
Select Person
from People RP
WHERE (SELECT COUNT(*) from People WHERE Person < RP.Person)
< (Select COUNT(*)/2 from People)
GO
CREATE VIEW SecondHalf
AS
Select Person
from People RP
WHERE (SELECT COUNT(*) from People WHERE Person > RP.Person)
< (Select COUNT(*)/2 from People)
GO
DECLARE @Pairing TABLE (FirstPerson varchar(20) UNIQUE, SecondPerson varchar(20) UNIQUE, CHECK(FirstPerson != SecondPerson))
DECLARE @HalfCount INT
SELECT @HalfCount = COUNT(*)/2 from People
WHILE (SELECT COUNT(*) from @Pairing) < (@HalfCount)
BEGIN
INSERT @Pairing
Select TOP 1 F.Person, (Select TOP 1 Person from SecondHalf ORDER BY NEWID())
from FirstHalf F
WHERE NOT EXISTS (SELECT 1 from @Pairing P WHERE F.Person = P.FirstPerson)
ORDER BY NEWID()
END

Select * from @Pairing
[/code]

The problem is it throws errors as it goes....

DavidM

"SQL-3 is an abomination.."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-19 : 17:35:03

Oh.. funny guys..

Why so needlessly complicate the subject?

Let's reduce it to N/2 random pairs without mentioning those gifts.

>The Enigma

Can you just add into my solution one more field and update it with
newid()? And then use namely this field in my WHERE clauses?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 17:40:28

quote:
Sure, you get 2 points for naming convention, but -3 for using a cursor.

Tough Crowd

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-19 : 17:50:54

I think gift exchanges is a great abstraction, superceeded only by paired flavors of ice cream, but if you prefer abstractions of numeric pairs that's fine too.

I was hoping Jeff would clarify which solution he wants. One that allows symmetrical pair duplicates and one that does not.

Sam

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-19 : 18:12:10

I just realised you don't have to use Views and also a solution that does not throw terrors..


DECLARE @Pairing TABLE (FirstPerson varchar(20) UNIQUE, SecondPerson varchar(20) UNIQUE, CHECK(FirstPerson != SecondPerson))
DECLARE @HalfCount INT 
SELECT @HalfCount = COUNT(*)/2 from People
WHILE (SELECT COUNT(*) from @Pairing) < (@HalfCount)
BEGIN
	INSERT @Pairing
	Select  TOP 1 F.Person, S.Person
	from People F CROSS JOIN People S
	WHERE F.Person != S.Person
	AND NOT EXISTS (SELECT 1 FROM @Pairing P WHERE F.Person IN (P.FirstPerson, P.SecondPerson) OR S.Person IN (P.FirstPerson, P.SecondPerson))
	ORDER BY NEWID()
END

Select * from @Pairing

I'll work on the 8 column solution now.. Got to love migration days.. [Twiddles thumbs]

EDIT: Must learn how to spell...

DavidM

"SQL-3 is an abomination.."

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-19 : 18:15:52

Create Table #Matches
(
rndid int identity,
person1 varchar(20),
person2 varchar(20)
)

INSERT Into #Matches (Person1)
SELECT a.Person
FROM People a
ORDER BY NewID()

UPDATE a
SET Person2 = b.Person1
FROM #Matches a
INNER JOIN #Matches b ON b.rndid = CASE
WHEN a.rndid + 1 > (SELECT Count(*) FROM People) THEN 1 ELSE a.rndid + 1 END

Select * fROM #Matches

Drop table #Matches

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-19 : 18:17:07

A slight modification from the solution should be OK...


DECLARE @Pairing TABLE (FirstPerson varchar(20) UNIQUE, SecondPerson varchar(20) UNIQUE, CHECK(FirstPerson != SecondPerson))
DECLARE @Count INT 
SELECT @Count = COUNT(*) from People
WHILE (SELECT COUNT(*) from @Pairing) < (@Count)
BEGIN
	INSERT @Pairing
	Select  TOP 1 F.Person, S.Person
	from People F CROSS JOIN People S
	WHERE F.Person != S.Person
	AND NOT EXISTS (SELECT 1 FROM @Pairing P WHERE F.Person = P.FirstPerson OR S.Person = P.SecondPerson)
	ORDER BY NEWID()
END

Select * from @Pairing

DavidM

"SQL-3 is an abomination.."

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-19 : 18:19:56

Nice Damian..

DavidM

"SQL-3 is an abomination.."

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources