Get index of certain result in query results list?

Question

I've been trying to even write this title in a way that makes most sense, but through much googling I have not found anything to match what I am looking.

Basically, I have a database that stores players and their different levels, and I already have a working query that fetches a list of all players and ranks them in the required order (Total prestige first, then level, then experience and lastly by the oldest update timestamp)

The query I use for this is:

SELECT u.id, u.username, u.mode, u.total_prestige as prestige, u.total_level as level, u.total_xp as exp, s.created FROM hs_users u JOIN hs_userskill s ON u.id = s.userId
WHERE s.id IN (SELECT MAX(id) FROM hs_userskill WHERE userId = u.id GROUP BY userId)
ORDER BY total_prestige DESC, total_level DESC, total_xp DESC, created ASC;

But now, on a different page, I need to find the players "rank" (so basically their index in the results list) Is there a proper sql way of doing this (probably), instead of just taking the whole results set into code and looping over it? As I am tempted at doing at this point in time.

My database structure in sql:

CREATE TABLE IF NOT EXISTS `hs_modes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `modename` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `modeicon` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `hs_skills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `skillname` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `skillicon` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `hs_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `mode` int(11) NOT NULL DEFAULT '0',
  `total_prestige` int(11) NOT NULL DEFAULT '0',
  `total_level` int(11) NOT NULL DEFAULT '0',
  `total_xp` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (`mode`) REFERENCES `hs_modes`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `hs_userskill` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `skillId` int(11) NOT NULL,
  `prestige` int(11) NOT NULL,
  `experience` int(11) NOT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`userId`) REFERENCES `hs_users`(`id`),
  FOREIGN KEY (`skillId`) REFERENCES `hs_skills`(`id`),
  UNIQUE KEY `userskill` (`userId`, `skillId`, `prestige`, `experience`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Maybe use [`ROW_NUMBER()`](https://stackoverflow.com/questions/1895110/row-number-in-mysql)? — kmoser, Sep 10 '21 at 20:23
You know, it doesn't hurt to ask if you want more info. What does hurt is unneccesary comments like this. I posted a screenshot as I figured it contained all the info to get an idea of the layout, whereas the code box is always so small and chunky. — Alex, Sep 10 '21 at 20:54

nbk · Accepted Answer · 2021-09-10T21:17:36.757

1

In mysql 8 you have ROW_NUMBER to signify the position in the specified order

SELECT 
    u.id,
    u.username,
    u.mode,
    u.total_prestige AS prestige,
    u.total_level AS level,
    u.total_xp AS exp,
    s.created
    ,ROW_NUMBER() OVER(ORDER BY total_prestige DESC , total_level DESC , total_xp DESC , created ASC) rn
FROM
    hs_users u
        JOIN
    hs_userskill s ON u.id = s.userId
WHERE
    s.id IN (SELECT 
            MAX(id)
        FROM
            hs_userskill
        WHERE
            userId = u.id
        GROUP BY userId)
ORDER BY total_prestige DESC , total_level DESC , total_xp DESC , created ASC;

Fro Bob rank

SELECT
    * 
FROM 
    (SELECT 
        u.id,
        u.username,
        u.mode,
        u.total_prestige AS prestige,
        u.total_level AS level,
        u.total_xp AS exp,
        s.created
        ,ROW_NUMBER() OVER(ORDER BY total_prestige DESC , total_level DESC , total_xp DESC , created ASC) rn
    FROM
        hs_users u
            JOIN
        hs_userskill s ON u.id = s.userId
    WHERE
        s.id IN (SELECT 
                MAX(id)
            FROM
                hs_userskill
            WHERE
                userId = u.id
            GROUP BY userId)
    ) t1
WHERE username = 'Bob'

edited Sep 10 '21 at 21:17

answered Sep 10 '21 at 20:23

nbk

45,398
8
30
47

I dont care about the index inside the database though, I care about the index in the results list, so if I were to get 10 results from my query, I want to specify where did player "Bob" end up in this top 10? – Alex Sep 10 '21 at 20:28
1

i was a bit confused with the index. i changed my answe – nbk Sep 10 '21 at 20:38
row_number ( rownum in Oracle) is the order of the records IN THE CURRENT QUERY - not somewhere else in the database. – Randy Sep 10 '21 at 20:41
Thank you, that seems to at least get me a step in the right direction. Is there also a way for mysql to then return the "rn" for where username='Bob'? – Alex Sep 10 '21 at 20:52
sure run the query abouve as subquery and show in the outer select only the row(s) you want with a where clause of course – nbk Sep 10 '21 at 21:15
Sorry, missed your edit, but I was thinking around the same thing and then noticed you posted this! So thank you again :) – Alex Sep 10 '21 at 22:07

score 0 · Answer 2 · answered Sep 10 '21 at 20:22

0

Set the database connection cursor to fetch the result as dictionary, then you can access the data as dictionary index of it's column names

answered Sep 10 '21 at 20:22

john mba

9
3

Not sure if you understand what I need, I need a seperate query that is called on a complete different page, that basically only computes what "rank" the player is at – Alex Sep 10 '21 at 20:24
In that case, you can use sub query, or store the first query as view then run some arithmetic query on the view as if it's a table. – john mba Sep 10 '21 at 20:36

Get index of certain result in query results list?

2 Answers2