SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Top Randomized Results with Multiple Qualifying Conditions


Top Randomized Results with Multiple Qualifying Conditions

Author
Message
busraker
busraker
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 140
I found this to be an interesting exercise for the day. I've been assigned the task of picking the "winners" in a lottery, which must be random, but the aggregate results need to meet multiple criteria.

It would be easy if there was just a "single" criteria .. you could just pick the top "n" lottery results until the criteria was met. But I need to consider multiple factors.

And yes I can easily do this with what is described as "RBAR" ...

The table:

CREATE TABLE #Ticket
(TicketID NUMERIC(18,0) PRIMARY KEY,
sRace CHAR(1),
iAge NUMERIC(18,0),
sParty CHAR(1))



Some data (I have thousands of records for each lottery)

INSERT INTO #Ticket
SELECT '18986', 'B', '40', 'D' UNION ALL
SELECT '39189', 'W', '63', 'R' UNION ALL
SELECT '42223', 'B', '46', 'D' UNION ALL
SELECT '44106', 'W', '82', 'R' UNION ALL
SELECT '44365', 'B', '52', 'D' UNION ALL
SELECT '45726', 'W', '42', 'D' UNION ALL
SELECT '53323', 'W', '49', 'D' UNION ALL
SELECT '58770', 'W', '70', 'R' UNION ALL
SELECT '59624', 'B', '33', 'D' UNION ALL
SELECT '59788', 'B', '36', 'D' UNION ALL
SELECT '77297', 'W', '72', 'R' UNION ALL
SELECT '81772', 'W', '66', 'R' UNION ALL
SELECT '90966', 'W', '76', 'R' UNION ALL
SELECT '95401', 'W', '56', 'R' UNION ALL
SELECT '96760', 'B', '39', 'D' UNION ALL
SELECT '101073', 'W', '68', 'R' UNION ALL
SELECT '107167', 'B', '44', 'R' UNION ALL
SELECT '108750', 'W', '57', 'R' UNION ALL
SELECT '123544', 'A', '69', 'R' UNION ALL
SELECT '124403', 'A', '44', 'R' UNION ALL
SELECT '124937', 'W', '49', 'R' UNION ALL
SELECT '126040', 'A', '56', 'R' UNION ALL
SELECT '127882', 'A', '75', 'R' UNION ALL
SELECT '128238', 'W', '26', 'R' UNION ALL
SELECT '132748', 'W', '77', 'R' UNION ALL
SELECT '133906', 'W', '35', 'D' UNION ALL
SELECT '134248', 'B', '37', 'R' UNION ALL
SELECT '136046', 'H', '43', 'D' UNION ALL
SELECT '136253', 'W', '55', 'R' UNION ALL
SELECT '138220', 'W', '52', 'D' UNION ALL
SELECT '140297', 'B', '76', 'R' UNION ALL
SELECT '140457', 'W', '36', 'D' UNION ALL
SELECT '148863', 'B', '62', 'R' UNION ALL
SELECT '148943', 'W', '69', 'R' UNION ALL
SELECT '148959', 'W', '57', 'R' UNION ALL
SELECT '151948', 'B', '64', 'R' UNION ALL
SELECT '152141', 'B', '46', 'D' UNION ALL
SELECT '153106', 'W', '53', 'R' UNION ALL
SELECT '156206', 'W', '52', 'R' UNION ALL
SELECT '160553', 'W', '60', 'R' UNION ALL
SELECT '161406', 'B', '27', 'D' UNION ALL
SELECT '161663', 'B', '25', 'D' UNION ALL
SELECT '161987', 'W', '36', 'R' UNION ALL
SELECT '162127', 'W', '61', 'R' UNION ALL
SELECT '181421', 'B', '27', 'D' UNION ALL
SELECT '181818', 'B', '42', 'D' UNION ALL
SELECT '181954', 'W', '57', 'R' UNION ALL
SELECT '182168', 'W', '66', 'R' UNION ALL
SELECT '182292', 'I', '47', 'R' UNION ALL
SELECT '182379', 'W', '44', 'R'



So I can simply "lotterize" the winners with a CTE like this:

WITH lottery AS
(SELECT
TicketID,
sRace,
sParty,
iAge,
ABS(CHECKSUM(NEWID()))%100000+1 AS iRandom
FROM
#ticket
)

SELECT TOP 5 * FROM lottery ORDER BY iRandom



**BUT**, I need to be assured that at least 80% of the "winners" have sRace = "W" **AND** 80% of the winners have sParty = "R"

So considering this sample result set (it will of course vary each time you run the code)

TicketID sRace sParty iAge iRandom
181954 W R 57 282
124937 W R 49 5866
96760 B D 39 7212
133906 W D 35 9853
140457 W D 36 15684
59788 B D 36 19464
108750 W R 57 20181
101073 W R 68 20440
161663 B D 25 20612
123544 A R 69 21100

80%+ (or 4+) must be sRace = "W" and 80%+ (or 4+) must be sParty = "R"
Or logicially (and more importantly from a coding perspective) no more than 1 of each can be other than that.

So the winners would have to be
TicketID sRace sParty iAge iRandom
181954 W R 57 282
124937 W R 49 5866
96760 B D 39 7212
108750 W R 57 20181
101073 W R 68 20440
busraker
busraker
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 140
I get a different random number every time I reference the CTE ?!?!


SELECT l1.ticketID, l1.iRandom, l2.iRandom FROM lottery l1 INNER JOIN lottery l2 ON l1.TicketID = l2.TicketID



Well, I guess I will need to use a temp table
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17087 Visits: 6431
I'd probably resort to a set-based loop.


DECLARE @ROWS INT = 0;
WHILE @ROWS = 0
BEGIN
WITH RandomWinners AS
(
SELECT TOP 5 *
FROM Ticket
ORDER BY NEWID()
)
SELECT *
INTO #RandomWinners
FROM RandomWinners;

IF EXISTS
(
SELECT sRace, sParty
FROM #RandomWinners
GROUP BY sRace, sParty
HAVING COUNT(CASE sRace WHEN 'W' THEN 1 END) >= 4 AND
COUNT(CASE sParty WHEN 'R' THEN 1 END) >= 4
)
SELECT *
FROM #RandomWinners;

SELECT @ROWS = @@ROWCOUNT;
DROP TABLE #RandomWinners;
END




Note that I have completely ignored political correctness w.r.t. supporting a lottery that seems to favor white republicans, on the basis that help on this forum is color blind.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search