CODEWITHSUNDEEP
mysqlsql
TtT23
TtT23

Reputation: 7040

SQL Get most frequent value from a column based on a condition

This query 

SELECT 
PlayerID, HeroTypeID, HeroTypeIDCount, Wins / (Losses + Wins) AS WinRate, Wins, Losses
FROM (
    SELECT E.PlayerID AS PlayerID, 
           FK_HeroTypeID AS HeroTypeID, 
           COUNT(FK_HeroTypeID) AS HeroTypeIDCount,
           SUM(CASE WHEN D.Result = 'LOSS' THEN 1 ELSE 0 END) AS Losses, 
           SUM(CASE WHEN D.Result = 'WIN' THEN 1 ELSE 0 END) AS Wins
        FROM GamePlayerDetail D
        JOIN Player E
            ON D.FK_PlayerID = E.PlayerID
        JOIN Game I
                ON D.FK_GameID = I.GameID
        WHERE PlayedDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
    GROUP BY E.PlayerID, FK_HeroTypeID
) AS T
ORDER BY PlayerID

produces the following result:

# PlayerID, HeroTypeID, HeroTypeIDCount, WinRate, Wins, Losses
'1', '11', '1', '1.0000', '1', '0'
'1', '13', '3', '0.3333', '1', '2'
'1', '24', '5', '0.8000', '4', '1'
'1', '27', '1', '1.0000', '1', '0'
'2', '28', '1', '0.0000', '0', '1'
'2', '6', '1', '0.0000', '0', '1'
'2', '30', '1', '0.0000', '0', '1'
'2', '7', '1', '1.0000', '1', '0'

What I'd like to do is get the most frequent FK_HeroTypeID (which is also highest value of HeroTypeIDCount) per PlayerID, but in case of ties, the highest winrate should take precedence. Here's an example of what I'd like to get:

PlayerID, HeroTypeID, HeroTypeIDCount, WinRate, Wins, Losses
       1,         24,               5,  0.8000,    4,      1
       2,          7,               1,  1.0000,    1,      0

How should you write a query like this?

Edit:

Ok, here's a simple Create/Insert table for the produced result.

http://sqlfiddle.com/#!9/d644a

Upvotes: 1

Views: 97

Answers (1)

Strawberry
Strawberry

Reputation: 33935

SELECT playerid
     , herotypeid
     , herotypeidcount
     , winrate
     , wins
     , losses 
  FROM 
     ( SELECT *
            , CASE WHEN @prev=playerid THEN @i:=@i+1 ELSE @i:=1 END rank
            , @prev:=playerid prev 
         FROM table1
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY herotypeidcount DESC
            , winrate DESC
     ) x 
 WHERE rank = 1;

Here's a 'hack' solution. It works, but really shouldn't be relied upon...

 SELECT * 
   FROM 
      ( SELECT * 
          FROM table1 
         ORDER 
            BY herotypeidcount DESC
             , winrate DESC
      ) x
  GROUP 
     BY playerid

Upvotes: 1

Related Questions