Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Top Randomized Results with Multiple Qualifying Conditions Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, September 24, 2013 1:58 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, April 02, 2014 11:24 AM Points: 17, Visits: 76
 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 #TicketSELECT '18986', 'B', '40', 'D' UNION ALLSELECT '39189', 'W', '63', 'R' UNION ALLSELECT '42223', 'B', '46', 'D' UNION ALLSELECT '44106', 'W', '82', 'R' UNION ALLSELECT '44365', 'B', '52', 'D' UNION ALLSELECT '45726', 'W', '42', 'D' UNION ALLSELECT '53323', 'W', '49', 'D' UNION ALLSELECT '58770', 'W', '70', 'R' UNION ALLSELECT '59624', 'B', '33', 'D' UNION ALLSELECT '59788', 'B', '36', 'D' UNION ALLSELECT '77297', 'W', '72', 'R' UNION ALLSELECT '81772', 'W', '66', 'R' UNION ALLSELECT '90966', 'W', '76', 'R' UNION ALLSELECT '95401', 'W', '56', 'R' UNION ALLSELECT '96760', 'B', '39', 'D' UNION ALLSELECT '101073', 'W', '68', 'R' UNION ALLSELECT '107167', 'B', '44', 'R' UNION ALLSELECT '108750', 'W', '57', 'R' UNION ALLSELECT '123544', 'A', '69', 'R' UNION ALLSELECT '124403', 'A', '44', 'R' UNION ALLSELECT '124937', 'W', '49', 'R' UNION ALLSELECT '126040', 'A', '56', 'R' UNION ALLSELECT '127882', 'A', '75', 'R' UNION ALLSELECT '128238', 'W', '26', 'R' UNION ALLSELECT '132748', 'W', '77', 'R' UNION ALLSELECT '133906', 'W', '35', 'D' UNION ALLSELECT '134248', 'B', '37', 'R' UNION ALLSELECT '136046', 'H', '43', 'D' UNION ALLSELECT '136253', 'W', '55', 'R' UNION ALLSELECT '138220', 'W', '52', 'D' UNION ALLSELECT '140297', 'B', '76', 'R' UNION ALLSELECT '140457', 'W', '36', 'D' UNION ALLSELECT '148863', 'B', '62', 'R' UNION ALLSELECT '148943', 'W', '69', 'R' UNION ALLSELECT '148959', 'W', '57', 'R' UNION ALLSELECT '151948', 'B', '64', 'R' UNION ALLSELECT '152141', 'B', '46', 'D' UNION ALLSELECT '153106', 'W', '53', 'R' UNION ALLSELECT '156206', 'W', '52', 'R' UNION ALLSELECT '160553', 'W', '60', 'R' UNION ALLSELECT '161406', 'B', '27', 'D' UNION ALLSELECT '161663', 'B', '25', 'D' UNION ALLSELECT '161987', 'W', '36', 'R' UNION ALLSELECT '162127', 'W', '61', 'R' UNION ALLSELECT '181421', 'B', '27', 'D' UNION ALLSELECT '181818', 'B', '42', 'D' UNION ALLSELECT '181954', 'W', '57', 'R' UNION ALLSELECT '182168', 'W', '66', 'R' UNION ALLSELECT '182292', 'I', '47', 'R' UNION ALLSELECT '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 iRandom181954 W R 57 282124937 W R 49 586696760 B D 39 7212133906 W D 35 9853140457 W D 36 1568459788 B D 36 19464108750 W R 57 20181101073 W R 68 20440161663 B D 25 20612123544 A R 69 2110080%+ (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 beTicketID sRace sParty iAge iRandom181954 W R 57 282124937 W R 49 586696760 B D 39 7212108750 W R 57 20181101073 W R 68 20440
Post #1498031
 Posted Tuesday, September 24, 2013 2:10 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, April 02, 2014 11:24 AM Points: 17, Visits: 76
 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
Post #1498034
 Posted Tuesday, September 24, 2013 8:24 PM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 6:57 PM Points: 3,590, Visits: 5,099
 I'd probably resort to a set-based loop.`DECLARE @ROWS INT = 0;WHILE @ROWS = 0BEGIN 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!
Post #1498106

 Permissions