Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Help with SQL command

    Here is my database (example)
    Tom 40
    Tom 55
    Tom 18
    Tom 24
    Tom 55
    Tom 64
    Tom 45
    Tom 50
    Tom 40
    Tom 55
    Tom 16
    Tom 16
    Tom 48
    Tom 72
    Larry 55
    Larry 35
    Larry 55
    Larry 40
    Larry 60
    Larry 30
    Larry 60
    Larry 40
    Larry 27
    Larry 18
    Larry 42
    Larry 42
    Timmy 40
    Timmy 35
    Timmy 12
    Timmy 55
    Timmy 40
    Timmy 40
    Timmy 35
    Timmy 20
    Timmy 16
    Timmy 56
    Timmy 28
    Timmy 32
    Timmy 42
    Timmy 48
    Jim 50
    Jim 45
    Jim 30
    Jim 18
    Jim 60
    Jim 45
    Jim 25
    Jim 40
    Jim 10
    Jim 48
    Jim 42
    Jim 42
    Joe 45
    Joe 40
    Joe 14
    Joe 10
    Joe 50
    Joe 50
    Joe 35
    Joe 18
    Joe 48
    Joe 40
    Joe 48
    The rankings for all events would be the following:
    1. Tom = 598
    2. Larry = 504
    3. Timmy = 499
    4. Jim = 455
    5. Joe = 398

    Here is my current code:
    Code:
    SELECT *, SUM(`nationalpoints`)  FROM `tournamentresults` GROUP BY `playername` ORDER BY SUM(`nationalpoints`) DESC
    I need another set of rankings that will only count the top 5 points results for each player and then rankings them based on that total. I need the rankings to show the following based on the example database above:
    1. Tom = 301
    2. Larry = 272
    3. Jim = 248
    4. Joe = 241
    5. Timmy = 241


    Regards,
    David

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So...I wasted my time...

    Code:
    ; WITH CTE AS (
    SELECT playername, nationalpoints
    	 , ROW_NUMBER() OVER (PARTITION BY playername ORDER BY nationalpoints DESC) AS RowNum
    FROM #tournamentresults
    )
    
    SELECT playername, SUM(nationalpoints) AS SUM_nationalpoints_Top5
    FROM CTE
    WHERE RowNum BETWEEN 1 AND 5
    GROUP BY playername
    
    WITH OrderedOrders AS
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes!

    Using ROW_NUMBER() must be a straitforward and an easy way to solve the issue.

    But. I couldn't find a ROW_NUMBER() function in MySQL manuals,
    for example in MySQL :: MySQL 5.6 Reference Manual :: 12 Functions and Operators

    And, I also couldn't find CTE.


    So, I tried another solution without using ROW_NUMBER() nor CTE.

    Although the following example might have some inconsistency with MySQL syntax and/or semantics,
    the main considerations/logics would be applicable on MySQL, too.


    Note(1): tested on DB2 9.7 for Windows.

    Example 1-d: Test Data
    Note(2): removed one row and added two rows for Timmy. and added players Spock and Serene.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM tournament_results;
    ------------------------------------------------------------------------------
    
    PLAYER_NAME NATIONAL_POINTS
    ----------- ---------------
    Tom                      40
    Tom                      55
    Tom                      18
    Tom                      24
    Tom                      55
    Tom                      64
    Tom                      45
    Tom                      50
    Tom                      40
    Tom                      55
    Tom                      16
    Tom                      16
    Tom                      48
    Tom                      72
    Larry                    55
    Larry                    35
    Larry                    55
    Larry                    40
    Larry                    60
    Larry                    30
    Larry                    60
    Larry                    40
    Larry                    27
    Larry                    18
    Larry                    42
    Larry                    42
    Timmy                    40
    Timmy                    35
    Timmy                    12
    Timmy                    55
    Timmy                    40
    Timmy                    40
    Timmy                    40
    Timmy                    40
    Timmy                    35
    Timmy                    20
    Timmy                    16
    Timmy                    56
    Timmy                    28
    Timmy                    32
    Timmy                    48
    Jim                      50
    Jim                      45
    Jim                      30
    Jim                      18
    Jim                      60
    Jim                      45
    Jim                      25
    Jim                      40
    Jim                      10
    Jim                      48
    Jim                      42
    Jim                      42
    Joe                      45
    Joe                      40
    Joe                      14
    Joe                      10
    Joe                      50
    Joe                      50
    Joe                      35
    Joe                      18
    Joe                      48
    Joe                      40
    Joe                      48
    Spock                    50
    Spock                    50
    Spock                    50
    Spock                    40
    Spock                    30
    Spock                    30
    Spock                    20
    Spock                    20
    Spock                    20
    Spock                    20
    Spock                    10
    Serene                   40
    Serene                   40
    Serene                   40
    Serene                   40
    Serene                   40
    Serene                   40
    Serene                   40
    Serene                   40
    Serene                   40
    Serene                   35
    Serene                   30
    
      86 record(s) selected.
    Example 1: Tested query
    Code:
    SELECT player_name
         , SUM( CASE
                WHEN equal_higher_count <  5 THEN
                     national_points
                ELSE 0
                END
              )
           +
           SUM( CASE
                WHEN equal_higher_count >= 5 THEN
                     national_points
                END
              )
           * ( 5                       - MAX(higher_count) )
           / ( MAX(equal_higher_count) - MAX(higher_count) ) /* =: equal_count */
           AS total_points
     FROM (SELECT player_name
                , national_points
                ,(SELECT COUNT(*)
                   FROM  tournament_results t2
                   WHERE t2.player_name     =  t1.player_name
                     AND t2.national_points >  t1.national_points
                 ) AS higher_count
                ,(SELECT COUNT(*)
                   FROM  tournament_results t2
                   WHERE t2.player_name     =  t1.player_name
                     AND t2.national_points >= t1.national_points
                 ) AS equal_higher_count
            FROM  tournament_results t1
          )
     WHERE higher_count < 5
     GROUP BY
           player_name
     ORDER BY
           total_points DESC
    ;
    Example 1-r: Result
    Code:
    ------------------------------------------------------------------------------
    
    PLAYER_NAME TOTAL_POINTS
    ----------- ------------
    Tom                  301
    Larry                272
    Jim                  248
    Joe                  241
    Timmy                239
    Spock                220
    Serene               200
    
      7 record(s) selected.
    Last edited by tonkuma; 05-05-12 at 11:41. Reason: Add "Serene" in test data and modify the query.

  4. #4
    Join Date
    Apr 2012
    Posts
    2
    wow....way beyond my ability with sql. Thank you both

    This is probably out of my league.
    Last edited by oche; 05-01-12 at 17:47.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you saw intermediate results step by sep, it might be easier to understand.

    (1) See player Spock:
    Code:
    SELECT * FROM tournament_results;
    ------------------------------------------------------------------------------
    
    PLAYER_NAME NATIONAL_POINTS
    ----------- ---------------
    Spock                    50
    Spock                    50
    Spock                    50
    
    Spock                    40
    
    Spock                    30
    Spock                    30
    
    Spock                    20
    Spock                    20
    Spock                    20
    Spock                    20
    
    Spock                    10
    (2) Result of subquery:
    Code:
           SELECT player_name
                , national_points
                ,(SELECT COUNT(*)
                   FROM  tournament_results t2
                   WHERE t2.player_name     =  t1.player_name
                     AND t2.national_points >  t1.national_points
                 ) AS "Higher count"
                ,(SELECT COUNT(*)
                   FROM  tournament_results t2
                   WHERE t2.player_name     =  t1.player_name
                     AND t2.national_points >= t1.national_points
                 ) AS "Equal or Higher cnt"
            FROM  tournament_results t1
    ;
    ------------------------------------------------------------------------------
    
    PLAYER_NAME NATIONAL_POINTS Higher count Equal or Higher cnt
    ----------- --------------- ------------ -------------------
    Spock                    50            0                   3
    Spock                    50            0                   3
    Spock                    50            0                   3
    
    Spock                    40            3                   4
    
    Spock                    30            4                   6
    Spock                    30            4                   6
    
    Spock                    20            6                  10
    Spock                    20            6                  10
    Spock                    20            6                  10
    Spock                    20            6                  10
    
    Spock                    10           10                  11
    (3) Apply WHERE condition and see values of each expressions in result column(before grouping).
    Code:
    SELECT player_name
         ,      CASE
                WHEN equal_higher_count <  5 THEN
                     national_points
                END
            AS "1st SUM value"
         ,
                CASE
                WHEN equal_higher_count >= 5 THEN
                     national_points
                END
           AS "2nd SUM value"
         ,   ( 5                       -     higher_count  ) AS "5 - higher_cnt"
         ,   (     equal_higher_count  -     higher_count  ) AS "equal count"
    
     FROM (SELECT player_name
    ...
    ...
            FROM  tournament_results t1
          )
     WHERE higher_count < 5
    ;
    ------------------------------------------------------------------------------
                                            (Multiplier)   (Divisor)
    PLAYER_NAME 1st SUM value 2nd SUM value 5 - higher_cnt equal count
    ----------- ------------- ------------- -------------- -----------
    Spock                  50             -              5           3
    Spock                  50             -              5           3
    Spock                  50             -              5           3
    
    Spock                  40             -              2           1
    
    Spock                   -            30              1           2
    Spock                   -            30              1           2
    (4) Apply GROUP BY clause.
    Code:
    SELECT player_name AS player
         , SUM( CASE
                WHEN equal_higher_count <  5 THEN
                     national_points
                END
              )
           AS "first SUM"
         , SUM( CASE
                WHEN equal_higher_count >= 5 THEN
                     national_points
                END
              )
           AS "second SUM"
         ,   ( 5                       - MAX(higher_count) ) AS "Multiplier"
         ,   ( MAX(equal_higher_count) - MAX(higher_count) ) AS "Divisor"
         , SUM( CASE
                WHEN equal_higher_count >= 5 THEN
                     national_points
                END
              )
           * ( 5                       - MAX(higher_count) ) /* Multiplier */
           / ( MAX(equal_higher_count) - MAX(higher_count) ) /* Divisor */
           AS "second value to be added"
     FROM (SELECT player_name
    ...
    ...
            FROM  tournament_results t1
          )
     WHERE higher_count < 5
     GROUP BY
           player_name
    ;
    ------------------------------------------------------------------------------
           (a)        (b)        (c)        (d)        (e) := (b) * (c) / (d)
    PLAYER first SUM  second SUM Multiplier Divisor    second value to be added
    ------ ---------- ---------- ---------- ---------- ------------------------
    Spock         190         60          1          2                       30

    Code:
    (a) := 50 + 50 + 50 + 40 (Top 4 points)
    (e) := 30                (5th point)
    Final value := (a) + (e) = 190 + 30 = 220
    Last edited by tonkuma; 05-03-12 at 08:38.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The following query might be a little easier to understand.

    Example 2: (Not tested.)
    Code:
    SELECT player_name
         , SUM( CASE
                WHEN higher_count + equal_count <= 5 THEN
                     national_points
                ELSE 0
                END
              )
           +
           SUM( CASE
                WHEN higher_count + equal_count >  5 THEN
                     national_points
                ELSE 0
                END
              )
           * ( 5 - MAX(higher_count) )
           / MAX(equal_count)
           AS total_points
     FROM (SELECT player_name
                , national_points
                ,(SELECT COUNT(*)
                   FROM  tournament_results t2
                   WHERE t2.player_name     = t1.player_name
                     AND t2.national_points > t1.national_points
                 ) AS higher_count
                ,(SELECT COUNT(*)
                   FROM  tournament_results t2
                   WHERE t2.player_name     = t1.player_name
                     AND t2.national_points = t1.national_points
                 ) AS equal_count
            FROM  tournament_results t1
          )
     WHERE higher_count < 5
     GROUP BY
           player_name
     ORDER BY
           total_points DESC
    ;
    Last edited by tonkuma; 05-05-12 at 11:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •