0

I need some help in mysql statement Ive table1 with 7 column and table 2 with 8 column the extra column named ranking , my statement should be like select all from table 1 then sort it by " number of users " insert it in table 2 and ranking start 1 2 3 etc, 

table 1 : 
username |     email         | number of users
jack          a@a.com               75
ralf          b@b.com               200
anne          c@c.com                12
sonny         d@d.com                300

===================================

here where i need to INSERT and RANKING based on number of users

table 2 

ranking    | username |     email         | number of users
1
2
3
Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
user172697
  • 155
  • 1
  • 11

3 Answers3

2

I would avoid to use another table. A single query suffices.

create table mytable (
id int not null auto_increment primary key,
username varchar(50),
email varchar(50),
number int
) engine = myisam;

insert into mytable (username,email,number)
values 
('a','aaa',10),
('b','bbb',30),
('c','ccc',50),
('d','ddd',30),
('e','eee',20),
('f','fff',45),
('g','ggg',20);

select @r:=@r+1 as rnk,username,email,number
from mytable,(select @r:=0) as r order by number desc

+------+----------+-------+--------+
| rnk  | username | email | number |
+------+----------+-------+--------+
|    1 | c        | ccc   |     50 |
|    2 | f        | fff   |     45 |
|    3 | b        | bbb   |     30 |
|    4 | d        | ddd   |     30 |
|    5 | e        | eee   |     20 |
|    6 | g        | ggg   |     20 |
|    7 | a        | aaa   |     10 |
+------+----------+-------+--------+
7 rows in set (0.00 sec)

This is a smarter version that considers ties

select @r:=@r + 1 as rn, username,email,
@pos:= if(@previous<>number,@r,@pos) as position,
@previous:=number as num
from mytable,(select @r:=0,@pos:=0,@previuos:=0) as t order by number desc 

+------+----------+-------+----------+--------+
| rn   | username | email | position | num    |
+------+----------+-------+----------+--------+
|    1 | c        | ccc   |        1 |     50 |
|    2 | f        | fff   |        2 |     45 |
|    3 | b        | bbb   |        3 |     30 |
|    4 | d        | ddd   |        3 |     30 |
|    5 | e        | eee   |        5 |     20 |
|    6 | g        | ggg   |        5 |     20 |
|    7 | a        | aaa   |        7 |     10 |
+------+----------+-------+----------+--------+
7 rows in set (0.00 sec)
Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
1
INSERT INTO table2
  SELECT @rank := @rank + 1, table1.* FROM table1
  JOIN( SELECT @rank := 0 ) AS init
  ORDER BY number_of_users DESC
nobody
  • 10,599
  • 4
  • 26
  • 43
  • 1 more thing , when i do this ranking 1st time , and try to re-rank another time , its says duplicated key @ ranking : 1 how we can avoid this when re-rank from time to time – user172697 Aug 14 '11 at 14:41
  • @user You can't do this twice because apparently your ranking column is UNIQUE which is a good thing, you don't want duplicate rankings in that column, table2 must be empty before running this query. You can empty table2 using this query: `TRUNCATE table2`. – nobody Aug 14 '11 at 14:59
  • so everytime i need to re-rank user ive to empty the table ! mm cant update ? – user172697 Aug 14 '11 at 15:04
  • 1
    @user With your current table structures I'd say yes. You may be able to write a complex UPDATE query that can do this incrementally but I suspect that would be a lot more expensive than just truncating the table and refilling it. – nobody Aug 14 '11 at 15:09
0

You need to do something like this:

SELECT * FROM `table1`
INNER JOIN `table2` USING ([a common filed name here])
ORDER BY table2.[the filed name here]

Good Luck!

  • Where is INSERT in table2? Question is pretty much vague so I suggest to wait for a more clear requirements – sll Aug 14 '11 at 13:00