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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply