Poker and SQL - 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)

Poker and SQL

Author

Topic

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-30 : 03:52:37

Ok folks, I am looking for an optimal solution to be able to deal random cards to players in a game of poker..

DDL & DML with inline comments...
I have admitted the RI for brevity
as well as the DiscardHands table...


--Holds All Cards (53) 1 Joker (JOK)
CREATE TABLE Cards(Card CHAR(3) NOT NULL PRIMARY KEY)
GO
--Data for Cards
DECLARE @Loop INT, @Suit CHAR(1)
SET @Loop = 1
WHILE @Loop <= 4 
BEGIN
	IF @Loop = 1 SET @Suit = 'H'
	IF @Loop = 2 SET @Suit = 'C'
	IF @Loop = 3 SET @Suit = 'D'
	IF @Loop = 4 SET @Suit = 'S'
	INSERT Cards (Card)
	SELECT 'A' + @Suit
	UNION ALL
	SELECT 'K' + @Suit
	UNION ALL
	SELECT 'Q' + @Suit
	UNION ALL
	SELECT 'J' + @Suit
	UNION ALL
	SELECT '10' + @Suit
	UNION ALL
	SELECT '9' + @Suit
	UNION ALL
	SELECT '8' + @Suit
	UNION ALL
	SELECT '7' + @Suit
	UNION ALL
	SELECT '6' + @Suit
	UNION ALL
	SELECT '5' + @Suit
	UNION ALL
	SELECT '4' + @Suit
	UNION ALL
	SELECT '3' + @Suit
	UNION ALL
	SELECT '2' + @Suit
	SET @Loop = @Loop + 1
END	
GO
Insert Cards (Card) values ('JOK')
GO
--Poker Game (Just the Players and the Game#)
CREATE TABLE Poker (UserID VARCHAR(25) NOT NULL, Game# INT NOT NULL
	CONSTRAINT PK_Poker PRIMARY KEY (UserID, Game#))
GO
--Data For a Single Game
--This design allows 1 to n players in a Game
Insert Poker (UserID, Game#) Values ('David', 1)
Insert Poker (UserID, Game#) Values ('Dingo', 1)
GO
--The Poker Hand
CREATE table PokerHands (UserID varchar(25) NOT NULL, Game# INT NOT NULL, Card CHAR(3) NOT NULL
	CONSTRAINT PK_PokerHands PRIMARY KEY (UserID, Game#, Card), 
CONSTRAINT IDX_GameCard UNIQUE (Game#, Card))
GO

So the do is.. Insert 5 cards for each player per Game

I would really like to do a way with a loop but for the life of me can not find a solution... Look at the second insert statement when the proc is executed, especially the number of rows for the PokerHand before the NestedLoop


CREATE PROC upPokerDeal
@Game# INT
AS
SET NOCOUNT ON
DECLARE @UserID VARCHAR(25)
SELECT TOP 1 @UserID = UserID FROM Poker WHERE Game# = @Game#
WHILE @@ROWCOUNT > 0
BEGIN
INSERT PokerHands (UserID, Game#, Card)
	SELECT TOP 5 @UserID, @Game#, Card
	FROM Cards C
	WHERE NOT EXISTS(SELECT 1 FROM PokerHands WHERE Game# = @Game# and Card = C.Card)
	ORDER BY NEWID()
	SELECT TOP 1 @UserID = UserID FROM Poker P WHERE Game# = @Game# 
		AND NOT EXISTS (SELECT 1 FROM PokerHands WHERE Game# = @Game# and UserID = P.UserID)
END
GO
EXEC upPokerDeal 1
SELECT * from PokerHands
TRUNCATE TABLE PokerHands
/*
DROP TABLE Cards
DROP TABLE Poker
DROP TABLE PokerHands
*/

DavidM

"SQL-3 is an abomination.."

claire
Starting Member

19 Posts

Posted - 2003-04-30 : 04:13:29

create table poker1(card varchar(2))
go
insert into poker1 select 'A'
insert into poker1 select '2'
insert into poker1 select '3'
insert into poker1 select '4'
insert into poker1 select '5'
insert into poker1 select '6'
insert into poker1 select '7'
insert into poker1 select '8'
insert into poker1 select '9'
insert into poker1 select '10'
insert into poker1 select 'J'
insert into poker1 select 'Q'
insert into poker1 select 'K'
go
select * from poker1
go
--------------------------------------------------------------------
CREATE TABLE Cards(Card CHAR(3) NOT NULL PRIMARY KEY)
GO
-------------------------------------------------------------------
DECLARE @Loop INT, @Suit CHAR(1)
SET @Loop = 1
WHILE @Loop <= 4
BEGIN
IF @Loop = 1 SET @Suit = 'H'
IF @Loop = 2 SET @Suit = 'C'
IF @Loop = 3 SET @Suit = 'D'
IF @Loop = 4 SET @Suit = 'S'
INSERT Cards (Card)
select card = card+@Suit from poker1
SET @Loop = @Loop + 1
END
--------------------------------------------------------------
You can modify the first part like this

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-04-30 : 05:03:53

Claire,

Thanks but the DDL & DML is just for reference and not part of the question itself.

DavidM

"SQL-3 is an abomination.."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-04-30 : 06:42:03

Don't know about optimal, but how does this suit you?

(my Numbers tally table starts at 0)


CREATE PROC upPokerDeal
@Game# INT
AS
DECLARE @Pack table(pos int IDENTITY(0, 1) PRIMARY KEY, Card char(3) NOT NULL)
INSERT INTO @Pack (Card)
SELECT Card FROM Cards ORDER BY NEWID()

INSERT INTO PokerHands
SELECT UserID, Game#, Card
FROM Poker AS P
CROSS JOIN Numbers AS N
INNER JOIN @Pack ON pos = N.n + 5*(
  SELECT COUNT(*) FROM Poker WHERE Game# = @Game# AND UserID < P.UserID)
WHERE Game# = @Game#
  AND N.n < 5
GO

Edited by - Arnold Fribble on 04/30/2003 06:43:19

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 08:07:40

Starting with Arnold's approach, first just insert the cards randomly into a table with an identity. (table var: @Pack; identity field: @pos)

Then, to deal out @C cards each to @N players:

SELECT (Pos % @n) + 1 as UserNumber, Game#, (Pos / n) + 1 as Card
FROM @Pack
where Pos <= @n * @c

(my math may be off slightly -- you may need to add or remove a +1 from the above -- I will check it when I get to work)

- Jeff

EDIT: Just noticed that the userID is a name, and not a number from 1 to @n. Just join the Usernumber returned above to your table of users in the game like this:

select userID, (select count(*) from Poker P2 WHERE P2.Game# = P.Game# and where P2.userID <= P.userID) as UserNumber
From Poker P
Where Game# = @Game#

(again, much like Arnold's)

I guess basically I am saying use a method like Arnold's, but no need for a tally table.

Edited by - jsmith8858 on 04/30/2003 09:20:03

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-04-30 : 08:30:49

Oops! How did that tally table get in there?

Obviously I wasn't very awake this morning.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 09:02:47

Arnold -- I'm supposed to be the guy who likes doing cross joins as much as possible !!!

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 09:13:11

note that in a poker game, the order of the players in the game is very important, so I assume that field would exist in the table for current users in a game.

In that case, that would be the # returned by my SQL -- and it makes the model more logically complete, because then if you have 6 players and deal out 9 cards each, based on the order the last 2 players will get 1 card less, as opposed to using the sort order of their username.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 11:17:26

I'm missing something...where is the randomness of all of this?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-30 : 11:25:38

Brett -- Try this:

SELECT * From AnyTable
ORDER BY NewID()

It randomly sorts a table! very cool trick.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 11:31:14

WOW that is SOOOOOOOOOOOOO

COOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOL

It just goes to show you learn something NEW

EVERY SINGLE G__ D____ DAY here at SQL Team

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources