In mysql how we can print all the details of a person having highest salary

Question

my sql

Answer 1

SELECT person.*, address.*
FROM person
INNER JOIN address ON person.addressId=address.Id
INNER JOIN
(SELECT a.city, MAX(p.salary) AS ms
FROM person AS p
INNER JOIN address AS a ON p.addressId = a.Id
GROUP BY a.city) AS m
ON address.city = m.city AND person.salary = m.ms

Main query gives us the list of person with addresses.
Subquery gives the list of cities with corresponding highest salaryes.
We inner join them on city and salary and get what we need.

Answer 2

if salary is stored in the same table where the rest of person's details

SELECT *
FROM person
WHERE salary = (SELECT MAX(salary) FROM person)

if salary is stored in separate table (e.g. position), with column person_id, bound to person.id column then

SELECT [put here list of required columns of person detailes]
FROM person AS p
INNER JOIN position AS pos ON pos.person_id = p.id
WHERE pos.salary = (SELECT MAX(salary) FROM position)

Answer 3

thanks 👦

Answer 4

how can we print person having higest salary from each city with his full detail

Answer 5

thanq