Mysql – Get count of value row

countMySQL

I'm trying to figure out what MySQL code would do the following.

Count * from 'users' table (eg; 0,7 rows) then within the count get the count of 'money' based on the value of the user. I'm struggling to explain, so I hope that I've gave as much detail, 1 more example below.

I'm trying to achieve this:

You are currently the eg 1st, 2nd (depending on money value). richest person on the website.

database example:

-------------------------------
 id  | username  | money
-------------------------------
 1   | name      | 1000
 2   | name2     | 3000
-------------------------------

Best Answer

This is an example of getting the rankings:

select version();

| version() |
| :-------- |
| 8.0.13    |

CREATE TABLE person( name CHAR(10), money INT );

✓

INSERT INTO person VALUES
  ('Smith',10),('Jones',15),('White',20),
  ('Black',40),('Green',50),('Brown',20);

✓

SELECT name, money, rank() OVER(win) AS 'Richest'
FROM person
WINDOW win AS (ORDER BY money DESC); 

name  | money | Richest
:---- | ----: | ------:
Green |    50 |       1
Black |    40 |       2
White |    20 |       3
Brown |    20 |       3
Jones |    15 |       5
Smith |    10 |       6

db<>fiddle here

Note this code is for MySQL-8.0 or MariaDB-10.2+. For earlier version implementations see this example

To get a value for a single user:

SELECT COUNT(*) + 1 AS "Richest"
FROM person p
JOIN person richer
  ON p.money < richer.money
WHERE p.name="Brown"

This doesn't have any version requirements.

Ref this fiddle.

Related Question