locked
Assign Points RRS feed

  • Question

  • User-962670764 posted
    2281	 	F         	Elite                    	R	23	53	516.40
    2305		F         	Elite                    	S	23	13	302.20
    2318	 	F         	Elite                    	S	20	42	306.70
    2282	 	M         	Elite                    	R	23	53	516.40
    2283		M         	Elite                    	R	23	49	505.10
    2280		M         	Elite                    	S	23	51	523.90
    2291		M         	Elite                    	S	23	46	413.40
    2300		M         	Elite                    	S	23	21	352.30
    2301	 	M         	Elite                    	S	23	36	336.50
    2307		M         	Elite                    	S	23	36	302.20
    2308		M         	Elite                    	S	23	59	283.70
    2306	 	M         	Junior                   	S	23	36	302.20
    2309	 	M         	Junior                   	S	23	46	253.20
    2292	 	M         	Master                   	S	23	49	413.40
    2314	 	M         	Master                   	S	22	42	203.40
    2316		M         	Master                   	S	21	35	226.40
    RIDERID                GENDER           CATEGORY               DIVISION       HOURS       MIN           DISTANCE

    This is the results table for a bike race. The most miles ridden is the winner. There are Gender categories, Divisions, and age groups, each with their own first place, 2nd Place, etc

    The highest mileage in each grouping is awarded 5 points, the next highest 4 points and the third, 3 points. If there are any other finishers, they get 1 point  each. If there are ties in the groupings both of the riders get the points. There is a points column in the table - anyone have any idea how to go about assigning those points using SQL?

    Friday, March 9, 2012 11:11 PM

Answers

  • User-62323503 posted

    In Table @TAB, I am just preparing data similar to your raceresults table. Then I am using CTE to generate ranks for all the riders by dividing them into gender, division and categoryid

    If you want to update umpoint field in raceresults table on the basis of the rank, then do like this. If required, please do the required modifications as per your requirement.

    ; WITH RANKS AS ( 
    	SELECT	eventid, riderid, gender, division, categoryid, distance
    			, DENSE_RANK() OVER (partition by gender, division, categoryid ORDER BY distance DESC) AS RANK 
    	FROM	raceresults
    )
    
    UPDATE	raceresults
    SET		umpoints = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END
    FROM	raceresults T INNER JOIN RANKS R ON T.eventid = T.eventid and R.riderid = R.riderid
    
    SELECT * FROM raceresults
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 10, 2012 10:40 PM

All replies

  • User-62323503 posted
    DECLARE @TAB TABLE(
    	RIDERID INT, GENDER CHAR(1), CATEGORY VARCHAR(10), DIVISION CHAR(1), HOURS INT, MIN INT, DISTANCE NUMERIC(10,2)
    )
    
    INSERT INTO @TAB
    SELECT 2281,'F','Elite','R',23, 53, 516.40
    UNION ALL SELECT 2305,'F','Elite','S',23, 13, 302.20
    UNION ALL SELECT 2318,'F','Elite','S',20, 42, 306.70
    UNION ALL SELECT 2282,'M','Elite','R',23, 53, 516.40
    UNION ALL SELECT 2283,'M','Elite','R',23, 49, 505.10
    UNION ALL SELECT 2280,'M','Elite','S',23, 51, 523.90
    UNION ALL SELECT 2291,'M','Elite','S',23, 46, 413.40
    UNION ALL SELECT 2300,'M','Elite','S',23, 21, 352.30
    UNION ALL SELECT 2301,'M','Elite','S',23, 36, 336.50
    UNION ALL SELECT 2307,'M','Elite','S',23, 36, 302.20
    UNION ALL SELECT 2308,'M','Elite','S',23, 59, 283.70
    UNION ALL SELECT 2306,'M','Junior','S', 23, 36, 302.20
    UNION ALL SELECT 2309,'M','Junior','S', 23, 46, 253.20
    UNION ALL SELECT 2292,'M','Master','S', 23, 49, 413.40
    UNION ALL SELECT 2314,'M','Master','S', 22, 42, 203.40
    UNION ALL SELECT 2316,'M','Master','S', 21, 35, 226.40
    
    ;WITH RANKS AS (
    	SELECT	RIDERID, GENDER, CATEGORY, DIVISION, HOURS, MIN, DISTANCE
    			, DENSE_RANK() OVER (ORDER BY DISTANCE DESC) AS RANK
    	FROM	@TAB
    )	
    
    SELECT	RIDERID, GENDER, CATEGORY, DIVISION, HOURS, MIN, DISTANCE
    		, POINTS = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END
    FROM	RANKS
    Friday, March 9, 2012 11:30 PM
  • User-962670764 posted

    That solution ranks all riders as one group. What is required is

    Group M,Elite,S

    2280 - 5 points, 2291 - 4 points, 2300 - 3 points  - all others in the group 1 point

    Group M, Elite, R

    2282 - 5 points, 2283 - 4 points - no others int that group

    Group F, Elite, R

    2281 - 5 points - no others in the group

    Group F, Elite, S

    2282 - 5 points, 2318 - 4 points

    And so on for each gender, category, division - That throws a little twist into it!

    Friday, March 9, 2012 11:49 PM
  • User-62323503 posted

    This would first divide all the riders on the basis of GENDER, CATEGORY and DIVISION and then would assign the ranks

    DECLARE @TAB TABLE(
    	RIDERID INT, GENDER CHAR(1), CATEGORY VARCHAR(10), DIVISION CHAR(1), HOURS INT, MIN INT, DISTANCE NUMERIC(10,2)
    )
    
    INSERT INTO @TAB
    SELECT 2281,'F','Elite','R',23, 53, 516.40
    UNION ALL SELECT 2305,'F','Elite','S',23, 13, 302.20
    UNION ALL SELECT 2318,'F','Elite','S',20, 42, 306.70
    UNION ALL SELECT 2282,'M','Elite','R',23, 53, 516.40
    UNION ALL SELECT 2283,'M','Elite','R',23, 49, 505.10
    UNION ALL SELECT 2280,'M','Elite','S',23, 51, 523.90
    UNION ALL SELECT 2291,'M','Elite','S',23, 46, 413.40
    UNION ALL SELECT 2300,'M','Elite','S',23, 21, 352.30
    UNION ALL SELECT 2301,'M','Elite','S',23, 36, 336.50
    UNION ALL SELECT 2307,'M','Elite','S',23, 36, 302.20
    UNION ALL SELECT 2308,'M','Elite','S',23, 59, 283.70
    UNION ALL SELECT 2306,'M','Junior','S', 23, 36, 302.20
    UNION ALL SELECT 2309,'M','Junior','S', 23, 46, 253.20
    UNION ALL SELECT 2292,'M','Master','S', 23, 49, 413.40
    UNION ALL SELECT 2314,'M','Master','S', 22, 42, 203.40
    UNION ALL SELECT 2316,'M','Master','S', 21, 35, 226.40
    
    ;WITH RANKS AS (
    	SELECT	RIDERID, GENDER, CATEGORY, DIVISION, HOURS, MIN, DISTANCE
    		, DENSE_RANK() OVER (PARTITION BY GENDER, CATEGORY, DIVISION ORDER BY DISTANCE DESC) AS RANK
    	FROM	@TAB
    )	
    
    SELECT	RIDERID, GENDER, CATEGORY, DIVISION, HOURS, MIN, DISTANCE
    	, POINTS = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END
    FROM	RANKS
    Saturday, March 10, 2012 1:49 AM
  • User-962670764 posted

    I guess that does divide anf assign the points, but you are having to do an individual select for every rider in the original insert into @TAB. You may as well just assign the points manually. I need something like this:

    DECLARE @FR0 TABLE ( EVENTID INT, RIDERID INT, DISTANCE NUMERIC(10,2),POINTS INT )
    DECLARE @FR1 TABLE ( EVENTID INT, RIDERID INT, DISTANCE NUMERIC(10,2),POINTS INT )
    DECLARE @FR2 TABLE ( EVENTID INT, RIDERID INT, DISTANCE NUMERIC(10,2),POINTS INT ) -- Will need @FR0, @FR1, @FR3,@FS0, @FS1, @MS0, ETC., For each combination of gender, division and category but that can be done if there is no alternative
    DECLARE @FR3 TABLE ( EVENTID INT, RIDERID INT, DISTANCE NUMERIC(10,2),POINTS INT ) 
    DECLARE @FR4 TABLE ( EVENTID INT, RIDERID INT, DISTANCE NUMERIC(10,2),POINTS INT ) 
    
    INSERT INTO @FR0
    SELECT eventid, riderid, distance, umpoints
    FROM  raceresults 
    WHERE gender = 'F' AND division = 2 AND categoryid = 0  
    
    ; WITH RANKS AS ( SELECT  EVENTID, RIDERID, DISTANCE, DENSE_RANK() OVER (ORDER BY DISTANCE DESC)  AS RANK FROM @FR0 )
    
    UPDATE raceresults SET umpoints = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END FROM RANKS  WHERE raceresults.riderid = RANKS.RIDERID AND raceresults.eventid = RANKS.EVENTID
    
    INSERT INTO @FR1
    SELECT eventid, riderid, distance, umpoints
    FROM  raceresults 
    WHERE gender = 'F' AND division = 2 AND categoryid = 1  
    
    ; WITH RANKS AS ( SELECT  EVENTID, RIDERID, DISTANCE, DENSE_RANK() OVER (ORDER BY DISTANCE DESC)  AS RANK FROM @FR1 )
    
    UPDATE raceresults SET umpoints = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END FROM RANKS  WHERE raceresults.riderid = RANKS.RIDERID AND raceresults.eventid = RANKS.EVENTID;
    
    
    INSERT INTO @FR2
    SELECT eventid, riderid, distance, umpoints
    FROM  raceresults 
    WHERE gender = 'F' AND division = 2 AND categoryid = 2 
    
    ; WITH RANKS AS ( SELECT  EVENTID, RIDERID, DISTANCE, DENSE_RANK() OVER (ORDER BY DISTANCE DESC)  AS RANK FROM @FR2 )
    
    UPDATE raceresults SET umpoints = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END FROM RANKS  WHERE raceresults.riderid = RANKS.RIDERID AND raceresults.eventid = RANKS.EVENTIDINSERT INTO @FR3
    
    SELECT eventid, riderid, distance, umpoints
    FROM  raceresults 
    WHERE gender = 'F' AND division = 2 AND categoryid = 3 
    
    ; WITH RANKS AS ( SELECT  EVENTID, RIDERID, DISTANCE, DENSE_RANK() OVER (ORDER BY DISTANCE DESC)  AS RANK FROM @FR3)
    
    UPDATE raceresults SET umpoints = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END FROM RANKS  WHERE raceresults.riderid = RANKS.RIDERID AND raceresults.eventid = RANKS.EVENTID
    
    INSERT INTO @FR4
    SELECT eventid, riderid, distance, umpoints
    FROM  raceresults 
    WHERE gender = 'F' AND division = 2 AND categoryid = 4 
    
    ; WITH RANKS AS ( SELECT  EVENTID, RIDERID, DISTANCE, DENSE_RANK() OVER (ORDER BY DISTANCE DESC)  AS RANK FROM @FR4 )
    
    UPDATE raceresults SET umpoints = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END FROM RANKS  WHERE raceresults.riderid = RANKS.RIDERID AND raceresults.eventid = RANKS.EVENTID
    
    SELECT * FROM raceresults
     
    That code, however only works if I run it one table at a time, it will error if you try to execute the whole series. But it does update points in  the raceresults table. But I am not familiar with RANKS. Is that a CTE?
    Saturday, March 10, 2012 6:14 AM
  • User-62323503 posted

    In Table @TAB, I am just preparing data similar to your raceresults table. Then I am using CTE to generate ranks for all the riders by dividing them into gender, division and categoryid

    If you want to update umpoint field in raceresults table on the basis of the rank, then do like this. If required, please do the required modifications as per your requirement.

    ; WITH RANKS AS ( 
    	SELECT	eventid, riderid, gender, division, categoryid, distance
    			, DENSE_RANK() OVER (partition by gender, division, categoryid ORDER BY distance DESC) AS RANK 
    	FROM	raceresults
    )
    
    UPDATE	raceresults
    SET		umpoints = CASE RANK WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 ELSE 1 END
    FROM	raceresults T INNER JOIN RANKS R ON T.eventid = T.eventid and R.riderid = R.riderid
    
    SELECT * FROM raceresults
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, March 10, 2012 10:40 PM