-1

I have a table called bank. I want to find what rank position a particular customer_number. For example my_id = 5 has a rank position of 1 because he is the richest. Then my_id = 4 would be rank 2

my_id | balance | customer_number
----------------|------------------
1     | 5       | 345345345
2     | 1       | 435456
3     | 45      | 345345
4     | 81      | 43543535
5     | 95      | 4566876
6     | 45      | 324624121

Where my_id = 6, would have rank of 3. And my_id = 3 would also have the rank of 3 because they are duplicates. Then rank of 4 would be given to my_id = 1.

I also want to select the balance and my_id at the same statement, but I can't seem to do so either.

I want a really fast and efficient way to do this.

select count(*) + 1, balance, my_id
from bank b
where b.balance > (select balance from bank where customer_number = 43543535);

select bank.*
from (select bank.*, rank() over (order by balance desc) as rnk
      from bank
     ) bank
where customer_number = 554113153538064391;
Zoey Malkov
  • 776
  • 6
  • 16

1 Answers1

1

If you just want the rank, then this query should be fine:

select count(*) + 1
from bank b
where b.balance > (select balance from bank where customer_number = 43543535);

For performance, you want an index on bank(customer_number) and bank(balance).

EDIT:

For all information, use JOIN:

select count(*) + 1, bb.*
from bank b join
     (select balance from bank where customer_number = 43543535) bb
     on b.balance >= bb.balance;

MySQL should allow the bb.*, even though this is an aggregation query. Formally, you should have group by bb.customer_number.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786