0

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
-------------------------------
3
  • So you are after a ranking, like this, or a more complete and simpler example. Feel free to answer yourself.
    – danblack
    Commented Feb 8, 2019 at 2:56
  • in your example, you want the name2 person first, as they have more money than name?
    – danblack
    Commented Feb 8, 2019 at 3:00
  • I know how to get the highest value of a row, I just don't understand how to get COUNT of how rich the user is, lets say I have a user named name2, and he has 5000, and 3 other users have 5000+ I'd like it to say you are the 4th richest by counting the rows?
    – Jordan
    Commented Feb 8, 2019 at 3:07

1 Answer 1

1

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.

3
  • Infact, it doesn't seem to work when I run this code.SELECT username, money, rank() OVER(win) AS 'Richest' FROM users WHERE username = 'lizzy' WINDOW win AS (ORDER BY money DESC) Lizzy is the second highest and it displays shes first by that code..
    – Jordan
    Commented Feb 8, 2019 at 3:30
  • Added. Please include a more complete example next time initially including sample input and expected output.
    – danblack
    Commented Feb 8, 2019 at 3:42
  • You're an absolute legend, thank you very much.
    – Jordan
    Commented Feb 8, 2019 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.