MySQL Forums
Forum List  »  Newbie

Re: Select *, MAX(Rank) from () as x, only returns one row??
Posted by: Peter Brawley
Date: February 09, 2022 09:00PM

The lowest rank is right there in the bottom row. To bring it out, you could re-arrange the query using CTEs ...

WITH 
  a AS (
    SELECT 
      PlayerId,
      SUM(Score) AS 'TScore',
      SUM(Kills) AS 'TKills',
      SUM(Deaths) AS 'TDeaths',
      SUM(Hits) AS 'THits',
      SUM(Shots) AS 'TShots',
      SUM(Headshots) AS 'THeadshots',
      SUM(Kills)/SUM(Deaths) as 'TKd',
      SUM(Hits)/SUM(Shots)*100 as 'TAc',
      SUM(Headshots)/SUM(Hits)*100 as 'TAcHs',
      COUNT(MinigameType) AS 'TSessions'
    FROM UserSessionData
    WHERE (ServerId LIKE 'eu-%' OR ServerId LIKE 'us-%' OR ServerId LIKE 'au-%') 
      AND (START >= (DATE_ADD(date_add(LAST_DAY(CURDATE()), interval 1 DAY), INTERVAL -1 MONTH))) 
      AND MinigameType='1'
    GROUP BY PlayerId 
  ) x
  WINDOW w AS (ORDER BY TScore DESC, TKd DESC, TKills DESC, TDeaths)
  ORDER BY `Rank`
},
 b AS (
    SELECT `rank` AS lowest FROM a ORDER BY `rank` DESC LIMIT 1
SELECT a.PlayerID, ..., a.`rank`, b.lowest 
FROM a CROSS JOIN b;

Options: ReplyQuote


Subject
Written By
Posted
Re: Select *, MAX(Rank) from () as x, only returns one row??
February 09, 2022 09:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.