October 18, 2007 at 8:08 am
Hello, I need help to chose the winner for each sample base on Max(RDN), but remove winner ID from next participation.
Rephrase: you won 1 sample and get out from next participation.
Can be create cursor, loop, ##table?
Case A: ID3 Case B: ID2 Case C: ID1 winner.
ID Sample RDN
1 A 2
2 A 4
3 A 7
1 B 1
2 B 3
3 B 6
1 C 3
2 C 2
3 C 5
Case A: ID3 is winner. select ID,Sample,MAX(RND) from T1 where Sample='A' group by ID,Sample
1 A 2
2 A 4
3 A 7
Case B: ID2 is winner, because ID3 won in case A and removed from the next cases:
select ID,Sample,MAX(RND) from T1 where Sample='B' and ID not in (select ID from T1 where Sample='A' group by ID) group by ID,Sample
1 B 1
2 B 3
Case C: ID1 is winner.
select ID,Sample,MAX(RND) from T1 where Sample='B' and (ID not in (select ID from T1 where Sample='A' group by ID) or ID not in (select ID,Sample,MAX(RND) from T1 where Sample='B' and ID not in (select ID from T1 where Sample='A' group by ID) group by ID,Sample) ) group by ID,Sample
1 C 3
October 18, 2007 at 8:27 am
you'll have to save the winners in a table somewhere, in order to exclude them.
create table PreviousWinners(id int, Sample varchar(10))
select T1.ID,T1.Sample,MAX(RND) from T1,
Left Outer Join PreviousWinners on T1.Id=PreviousWinners.Id AND T1.Sample = PreviousWinners.Sample
where T1.Sample='A'
And PreviousWinners.Id Is NULL
group by T1.ID,T1.Sample
Lowell
October 18, 2007 at 8:36 am
Exactly.
Another option might be to keep them in the call app and pass them in as either a delimited list or XML list and then either turn the delimited list into a table using a UDF or shred the XML as part of the join in the same way as you'd use the table or UDF.
Table is probably easier choice.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 18, 2007 at 9:47 am
OK, I spent WAY to much time on this. Primarily because I was stubborn and tried to find a way to do this in one statement. In the end I had to give up with that (Recursive queries can't perform aggregates :angry: ) but by that time I was already determined and this is what I came up with.
This will work with any number of samples, and I don't think it's to poorly written...
-- create some sample data
DECLARE @table TABLE (userID INT, sample CHAR(1), RDN INT)
INSERT @table
SELECT 1, 'A', 2 UNION ALL
SELECT 2, 'A', 4 UNION ALL
SELECT 3, 'A', 7 UNION ALL
SELECT 4, 'A', 5 UNION ALL
SELECT 5, 'A', 8 UNION ALL
SELECT 6, 'A', 9 UNION ALL --
SELECT 1, 'B', 1 UNION ALL
SELECT 2, 'B', 9 UNION ALL --
SELECT 3, 'B', 6 UNION ALL
SELECT 4, 'B', 2 UNION ALL
SELECT 5, 'B', 5 UNION ALL
SELECT 6, 'B', 7 UNION ALL
SELECT 1, 'C', 3 UNION ALL
SELECT 2, 'C', 4 UNION ALL
SELECT 3, 'C', 2 UNION ALL
SELECT 4, 'C', 9 UNION ALL --
SELECT 5, 'C', 4 UNION ALL
SELECT 6, 'C', 0 UNION ALL
SELECT 1, 'D', 3 UNION ALL
SELECT 2, 'D', 4 UNION ALL
SELECT 3, 'D', 2 UNION ALL
SELECT 4, 'D', 9 UNION ALL --
SELECT 5, 'D', 8 UNION ALL
SELECT 6, 'D', 0 ;
-- now the actual processing
DECLARE @samples TABLE (rn INT, sample CHAR(1)) -- create a table to hold the distinct samples
DECLARE @winners TABLE (sample CHAR(1), winner INT, RDN INT) -- create the winners table
DECLARE @inc INT -- a generic counter variable
-- populate the samples
INSERT @samples (sample, rn)
SELECT DISTINCT sample, rn = ROW_NUMBER() OVER(ORDER BY sample)
FROM @table
GROUP BY sample
-- initialize the counter
SET @inc = 1
-- get the winners
WHILE @inc <= (SELECT MAX(rn) FROM @samples)
BEGIN
INSERT @winners
SELECT
a.sample
,a.userID AS winnerUserID
,a.RDN AS winningRDN
FROM
@table a
INNER JOIN @samples s
ON s.sample = a.sample
INNER JOIN (SELECT
b.sample,
MAX(b.RDN) AS RDN
FROM
@table b
LEFT JOIN @winners w
ON w.winner = b.userID
WHERE
w.winner IS NULL
GROUP BY
b.sample) c
ON a.RDN = c.RDN
AND a.sample = c.sample
WHERE
s.rn = @inc
SET @inc = @inc + 1
END
-- return the results
SELECT
sample, winner, RDN
FROM
@winners
ORDER BY
sample
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 19, 2007 at 6:34 am
Thanks a lot. It works
October 20, 2007 at 5:58 pm
"IT"? What is "IT"? Jason's code?
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply