0

So I currently have a table in my database called 'users'.

Lets say 'users' has two columns: 'Email' and 'ReferralCount'.

Example:

Email     ReferralCount
1@1.com   5
2@2.com   3
3@3.com   7

What I want to be able to do, is rank the data in this table, based on their referral count, whereby the highest referral count is rank 1 and so on. Then I need to be able to get the rank of that user, based on their email address.

I have only used basic SQL and so I am not so sure how to do this?

Any help much appreciated!

fldchris
  • 13
  • 5
  • Write a select query & do order by ReferralCount in descending order. – manian May 01 '17 at 18:25
  • you should create view based on the referral count and put rank there, once your view created store in cache and fire query from there, if there any update in your user than again create new view. – JiteshNK May 01 '17 at 18:31
  • How do you want to handle ties? Say 2@2.com was also 5 referal... are they both rank 2 or one 2 other 3? are they both 3? – xQbert May 01 '17 at 18:37
  • Wow, there is a fantastic example in another stackoverflow, on this address here, which will really show you something valuable to you: http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select - enjoy, and feel free to ask questions... – David Svarrer May 01 '17 at 19:38
  • @xQbert I would like the rank to be unique, by this I mean only one row per rank position, so if two users had the 5 referrals, they would be rank 2 and 3. Thanks! – fldchris May 02 '17 at 19:34
  • so the one with the email address alphabetically earlier would be first; as you've not provided criteria to define what order each rank should be assigned. – xQbert May 02 '17 at 19:43

2 Answers2

2

This depends a bit on what you mean by "rank". The following gets the "dense rank", so ties all have the same value:

select 1 + count(distinct u.referralcount)
from users u
where u.referralcount > (select u2.referralcount from users u2 where u2.user = @email);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Sorry new to this, however wanted to know is it good to have subquery in any query ?? @Gordon Linoff – JiteshNK May 01 '17 at 18:33
  • 1
    Subqueries are fine to use, and are often the only way to build certain queries. `Join`ing two instances of the users table would be another way to tackle this problem, but subqueries are just fine. – RToyo May 01 '17 at 18:45
0

Edit: Apologies, Gordon Linoff already answered the question more directly. This is what I get for being half distracted while answering. :)

You could use a subquery to grab the ReferralCount for the Email in question, and then get a count of how many user rows have a higher Referral Count.

Your subquery would get the ReferralCount of the user (eg "5" for 1@1.com): select ReferralCount from users where Email=1@1.com.

Then if you use that as a subquery in a where clause, you could count how many users have a higher ReferralCount (remember to add 1 to the rank. If it returns "1" higher user, then you should output "2" as the current user's rank):

select 
    (count(*) + 1) as Rank
from
    users
where
    ReferralCount > (select ReferralCount from users where Email=1@1.com)

This will output "2", because only 3@3.com has more referrals than 1@1.com.

You may want to skip users with the same ReferralCount. Eg if three users all have "10" referrals, and the user you're querying has "9", then the above query would output a Rank of "4", even though 9 referrals is only in second place behind 10. If you would rather it return a rank of "2" (second place), then you could get a distinct count of the ReferralCount:

select 
    (count(distinct ReferralCount) + 1) as Rank
from
    users
where
    ReferralCount > (select ReferralCount from users where Email=1@1.com)
RToyo
  • 2,877
  • 1
  • 15
  • 22
  • You are not providing a ranking on each column - another stackoverflow entry really provides it: http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select - check it out and if you agree, you can maybe just edit your answer? – David Svarrer May 01 '17 at 19:41
  • @DavidSvarrer thanks for pointing me to that answer. However, I'm not seeing a direct connection between that question and OP's question; perhaps you can elaborate a little bit more? My understanding was that OP's question was asking about what rank an individual row falls under, whereas your linked question just wants a row number to be generated for multiple returned rows. Essentially OP is asking for a row's rank within a whole table, while that question asks for a row's rank within a result set. Perhaps I've overlooked something? – RToyo May 02 '17 at 16:04
  • 1
    @RobbieToyota Correct I am asking for a row's rank within a whole table. – fldchris May 02 '17 at 17:48