I want to show it in the first output if the first rank equals 1 in my database

SELECT id, name,password,oldname,number,
RANK() OVER(ORDER BY oldname="mxmx" DESC),
RANK() OVER(ORDER BY number DESC)
as "Rank1"
 FROM account

For example like this:

enter image description here

please guide me to reach a conclusion

CodePudding user response：

If you want the first-ranked items to appear first in the results, use:

SELECT
    id,
    name,
    password,
    oldname,
    number,
    RANK() OVER (ORDER BY oldname="mxmx" DESC),
    RANK() OVER (ORDER BY number DESC) AS "Rank1"
FROM account
ORDER BY 
    RANK() OVER (ORDER BY oldname="mxmx" DESC)

Alternatively, you could wrap your original query inside a SELECT * FROM (...) table_alias ORDER BY rank_alias if you prefer. (Though you should select the actual columns you need -- I write the * for brevity only)

PS. Additionally, I found the order of the RANK's themselves returns the results ordered "last-RANK-first", for MySQL 8 at least. Though I can't find a reference that would guarantee this behaviour, your query would be:

SELECT
    id,
    name,
    password,
    oldname,
    number,
    RANK() OVER (ORDER BY number DESC) AS "Rank1",
    RANK() OVER (ORDER BY oldname="mxmx" DESC)
FROM account