MySQL, get->sort->save

Question

MySQL

Hello.

Faced a question. I write top. There is a table:

tabl1:

login rrr
user ghfgh
user2 fgfg
user fgfgy
user3 ghgh
user2 fgfga

I need to calculate how many rows someone has.
user 2
user2 2
user3 1

sort by count and write/update to another table.

This action will update by cron.

The result should be tabl2:

top login
1 user
2 user2
3 user3

Answer 1

SELECT COUNT(a.`login`) as amount, a.`login` as login FROM table1 GROUP BY login ORDER BY amount ASC

Answer 2

First, did I understand correctly that login is the primary key for tabl2? And then he is second on your list, which makes me think.
Secondly, I correctly understood that the table should be updated if the primary key already exists?
In general, if I understand everything correctly, I can suggest doing something like this:

INSERT INTO tabl2 (login, top)
SELECT * FROM (SELECT login, COUNT(*) AS cnt FROM tabl1 GROUP BY login) AS t1
ON DUPLICATE KEY UPDATE top = t1.cnt;

Answer 3

what for to sort before record in other table if it is possible to sort at a stage of an output of the information from the resulting table?
Give up intermediate sorting and everything will immediately become easy