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
-------------------------------
  • So you are after a ranking, like this, or a more complete and simpler example. Feel free to answer yourself. – danblack Feb 8 at 2:56
  • in your example, you want the name2 person first, as they have more money than name? – danblack Feb 8 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 Feb 8 at 3:07
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.

  • 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 Feb 8 at 3:30
  • Added. Please include a more complete example next time initially including sample input and expected output. – danblack Feb 8 at 3:42
  • You're an absolute legend, thank you very much. – Jordan Feb 8 at 3:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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