There is a table:

id, login, basic_account, full_name, actual_address, last_change_date

The values ​​in the login column may repeat, the values ​​in the last_change_date column reflect the date.

Task: create a new one in which login and date maximum last_change_date corresponding to login are not repeated. And accordingly id, basic_account, full_name, actual_address correspond to last_change_date

the number of rows in the summary table must be exactly the number of unique login values.

my attempt is not optimal:

 SELECT DISTINCT (login) AS login2, (SELECT MAX(last_change_date) FROM users WHERE login = login2) AS last_change_date2 (SELECT id FROM `users` WHERE last_change_date = last_change_date2) AS id (SELECT basic_account FROM `users` WHERE last_change_date = last_change_date2) AS basic_account, (SELECT full_name FROM `users` WHERE last_change_date = last_change_date2) AS full_name (SELECT actual_address FROM `users` WHERE last_change_date = last_change_date2) AS actual_address FROM `users` `users` ORDER BY login2 DESC;

    1 answer 1

     select * from users where (login, last_change_date) in( select login, max(last_change_date) from users group by login )