MySQL Forums
Forum List  »  Newbie

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

...select x, max(x)... without a Grouo By clause will return one row.

But ...WINDOW w AS (ORDER BY TScore DESC, TKd DESC, TKills DESC, TDeaths) will fail on the error that TScore &c are unknown columns since they're aliases. You'd need something like ...

SELECT TScore, ..., Row_Number() OVER w as `Rank`
FROM (
  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`;

Options: ReplyQuote


Subject
Written By
Posted
Re: Select *, MAX(Rank) from () as x, only returns one row??
February 09, 2022 01: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.