mysql query prints data of different rows in one row

I am using MySQL version 8.0 and popSQL as the editor.

Table:

create table account (no int, salary int);


insert into account values
(901,25000),
(902,30000),
(903,21000),
(904,40000),
(905,27000);

Now, I want no and salary of person with highest salary. Highest salary is 40000 and corresponding no is 904. But, this query is giving me different output.

-- no and salary of max salary
select no, max(salary)
from account ;

The output is: no max(salary) 901 40000

It is printing the first no and the max salary. i.e. data of different rows are shown in a same row. How to fix this???

Same type of problem for my next query.

-- no and salary of second highest salary.
select no, max(salary)
from account
where salary not in
(select max(salary) from account);

The output is: no max(salary) 901 30000

Whereas expected is 902,30000.

I searched through the stack overflow for issue where data of different rows are shown in one but didn't get any help.

Thanking you in advance.

Solution

For Highest Salary

select no, salary from (
    select no, salary, ROW_NUMBER() OVER (partition by salary order by salary desc) as rn
    from account
) as a
where rn = 1 limit 1

For Second highest salary

select no, salary from (
    select no, salary, ROW_NUMBER() OVER (partition by salary order by salary desc) as rn
    from account
) as a
where rn = 2 limit 1