Mysql sort and define operation

Question

I need help with mysql Ive table1 with 7 columns and table 2 with 8 columns - an extra column called rank, my statement should look like select everything from table 1, then sort it by “number of users”, paste it into table 2 and start ranking 1 2 3, etc.,

table 1 : username | email | number of users jack [e-mail] 75 ralf [e-mail] 200 anne [e-mail] 12 sonny [e-mail] 300 

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

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

 table 2 ranking | username | email | number of users 1 2 3 

Answer 1

 INSERT INTO table2 SELECT @rank := @rank + 1, table1.* FROM table1 JOIN( SELECT @rank := 0 ) AS init ORDER BY number_of_users DESC 

Answer 2

I would not use another table. One request is enough.

 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 treats links.

 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) 

Answer 3

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