MYSQL Rank partition

Question

i have this table

| Name      | Born      | Live      | Age   |
|--------   |-------    |--------   |-----  |
| John      | UK        | UK        | 10    |
| Smith     | UK        | France    | 12    |
| Ben       | Spain     | Spain     | 15    |
| George    | Spain     | UK        | 25    |
| Ann       | UK        | UK        | 27    |
| Cassie    | Spain     | Spain     | 30    |

and i want this

| Name      | Born      | Live      | Age   | | Rank |
|--------   |-------    |--------   |-----  | |------|
| John      | UK        | UK        | 10    | |  1   |
| Ann       | UK        | UK        | 27    | |  2   |
| Smith     | UK        | France    | 12    | |  1   |
| Ben       | Spain     | Spain     | 15    | |  1   |
| Cassie    | Spain     | Spain     | 30    | |  2   |
| George    | Spain     | UK        | 25    | |  1   |

So i want a rank groupping by born and live.

I know that in SQL we can mae this using RANK AND PARTITION, but in MYSQl i cannot find a way to do this. Anyone can help?

score 2 · Answer 1 · edited Aug 22 '17 at 15:28

2

SELECT Name, Born, Live,Age, 
   @rank := IF(@prev_born = Born, @rank + 1,1) AS Born_rank,
   @prev_Born := Born
FROM TABLE,(select @prev_born :=0,@rank :=0)  r
ORDER BY Born, Age ASC

edited Aug 22 '17 at 15:28

Bugs

4,491
9
32
41

answered Aug 22 '17 at 15:01

ankam ranjithkumar

21
3

Although this code might work, it will be much more useful to the person asking the question if you could provide some insights... – joanolo Aug 23 '17 at 04:07

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

I think you should have a look at : Rank function in MySQL

Hope it will help you ;) !

edited May 23 '17 at 12:15

Community

1
1

answered Sep 29 '16 at 15:56

KatharaDarko

259
2
15

I saw that answer, but its just a rank not a rank partition – user1851366 Sep 29 '16 at 15:56
Sorry, my bad ;) have you seen this one ? http://stackoverflow.com/questions/3162389/multiple-ranks-in-one-table/ May it suit your needs – KatharaDarko Sep 29 '16 at 16:11
Nop easy, since they just want gruping with 1 column, and i want 2 – user1851366 Sep 29 '16 at 16:36

MYSQL Rank partition

2 Answers2