CODEWITHSUNDEEP
mysql
Dilukshan Mahendra
Dilukshan Mahendra

Reputation: 3398

Select the row with maximum value for a column MySQL

I have a table name 'employee' and I need to get the row with the maximum value for Salary and where the employee is from 'New York'.

select * from employee where city = 'New York' and MAX(salary);

Seems not working, Please suggest me a way

enter image description here

Upvotes: 1

Views: 80

Answers (3)

Minoru
Minoru

Reputation: 1730

Like this, you will get all the employees from New York that has the highest salary. I.E. if you have another employee with salary = 2000, you will get it too.

SELECT * FROM employee WHERE city = 'New York' AND salary = MAX(salary);

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13455

Try this::

select 
* 
from employee 
where city = 'New York' 
order by Salary desc limit 1

Upvotes: 5

naveen goyal
naveen goyal

Reputation: 4629

It is simple method. you can also use group by then sub query

select * from employee where city = 'New York' order by Salary desc limit 1

Upvotes: 2

Related Questions