+15 votes
by (100 points)

I have a point table with some columns being:

| user_id | points |
--------------------
|   1     |   10   |
|   5     |   10   |
|   5     |   50   |
|   3     |   15   |
|   3     |   10   |

I would like to get the rank for each user with MySQL.

I've seen this post MySQL get row position in ORDER BY but it doesn't have a SUM in it and I can't get it to work with SUM.

I would like to be able to get the rank of a given user_id.

Thanks in advance for your help!

10 Answers

+10 votes
by (100 points)

Reminding the OP's question:

I would like to be able to get the rank of a given user_id.

In order to actually perform operations over the @rank you have to user another derived table (yes, it is inefficient, that's why it is better not to handle this in MySQL):

SELECT * FROM (
  SELECT s.*, @rank := @rank + 1 rank FROM (
    SELECT user_id, sum(points) TotalPoints FROM t
    GROUP BY user_id
  ) s, (SELECT @rank := 0) init
  ORDER BY TotalPoints DESC
) r
WHERE user_id = 3

Output

| USER_ID | TOTALPOINTS | RANK |
|---------|-------------|------|
|       3 |          25 |    2 |

The process is basically:

  1. Get the total amounts of points per user
  2. Sort by those total points ranking
  3. Filter once you have the rank (otherwise, the rank will be compromised)

Fiddle here .

+9 votes
by (100 points)

Try this::

SELECT @rownum:[email protected] + 1 as row_number, 
       t.*
FROM ( 

select user_id, SUM(points) as Addedpoint
from mytable group by user_id order by Addedpoint desc

) t,
(SELECT @rownum := 0) r
+5 votes
by (100 points)

You can achieve that with subquery, inside which you should calculate your sum:

SELECT 
  @rank:[email protected]+1 AS rank, 
  user_id, 
  total_points 
FROM 
  (SELECT 
    user_id, 
    SUM(points) AS total_points 
  FROM t 
  GROUP BY 
    user_id) AS sum_points 
CROSS JOIN 
  (SELECT @rank:=0) AS init 
ORDER BY 
  sum_points.total_points DESC

-see my fiddle .

+5 votes
by (100 points)
select 
@rownum := @rownum + 1 AS position, 
user_id, 
total_points

from
(select user_id, sum(points) as total_points from table)a 
join 
(SELECT @rownum := 0) r
order by total_points desc
+3 votes
by (100 points)

Using a user variable you can do something like this:-

SELECT user_id, tot_points, @Rank:[email protected] + 1 AS user_rank
FROM
(
    SELECT user_id, SUM(points) AS tot_points
    FROM SomeTable
    GROUP BY user_id
    ORDER BY tot_points DESC
) Sub1
CROSS JOIN (SELECT @Rank:=0) Sub2
0 votes
by (100 points)

http://sqlfiddle.com/#!2/d0be9/1

SET @rank=0;
select @rank:[email protected]+1 AS rank, pointsScored.user_id, sumPoints
from (
   select user_id , SUM(points)as sumPoints
   from point
   group by user_id 
   order by  sumPoints desc
)as pointsScored
0 votes
by (100 points)

This is an old ask, but I came upon this in a Google search and was unhappy with the provided answers. I found the real answer in python-gnupg's GitHub issues:

gpg.encrypt(data, symmetric='AES256', passphrase=passphrase, armor=False, encrypt=False)

So, drop the recipients=None and add an encrypt=False . Your crypt.data will then contain the encrypted data. Unintuitive, but it works.

(src: https://github.com/isislovecruft/python-gnupg/issues/110 )

0 votes
by (100 points)

You probably downloaded the html github uses to display a notebook. To download the notebook itself, you can use the "raw" file link in github.

0 votes
by (100 points)

From Android Developers I saw that there is no tag for Bitmap XML that allow you to change it size. You need to do this via some Graphic Applications like GIMP. As @Kevin Cooper told already, ideally you need to create different sets of resources (with different sizes) for different screen types.

But still you can resize it using width and height attributes in ImageView , instead of creating bitmap with different size:

<ImageView
    android:layout_width="50dp"
    android:layout_height="50dp"
    android:scaleType="center"
    android:src="@drawable/Icon" />

If you want to reuse this image in other places, you can create separate style and reuse it:

// in styles.xml
<style name="Icon24x24">
    <item name="android:src">@drawable/Icon</item>
    <item name="android:gravity">center</item>
    <item name="android:layout_width">24dp</item>
    <item name="android:layout_height">24dp</item>
</style>

// in layout.xml
<ImageView style="@style/Icon24x24"/>

Another way of reusing, may be creating of separate layout file for one ImageView and then including in to other layouts.

If you want to use this bitmap inside of layer list, please see @Kevin Cooper answer, as he suggested it is possible to add paddings to layer list item :

<layer-list xmlns:android="http://schemas.android.com/apk/res/android">
    <item
        android:left="20dp"
        android:right="20dp">
        <bitmap
            android:src="@drawable/Icon"
            android:gravity="center" />
    </item>
</layer-list>

And as @Nicolo also pointed, form API 23, it is possible to set android:width and android:height" to item.

0 votes
by (100 points)

Just delete bootmgr... you wont be able to boot, and theres no bsod, too

No related questions found

...