Move the clicked element to first

I am working on a query where I need to find all the employee names whose salary is less than the other employee salaries. In output I need two columns, the names of both the employees.

for example:

I have a Table with 3 columns

ID  Name        Salary

1   John        7400
2   William     7200
3   Tom         7500

Now in output, I need Name of employee who earns less than other employee, and another column which is Name of other higher employees who earns higher that this employee.

Then sort the result by employee id who earns less and then by employee salary of higher earning employee

So output should be:

John Tom
William John
William Tom

Explanation:

John has less ID, also he has less salary than Tom, so he comes first.

Next lower ID employee is William, whose salary is less than John and Tom.

How to build a query for this scenario.

I am using MYSQL 5.

Answer

You could self-join the table:

select t1.name, t2.name
from mytable t1
inner join mytable t2 on t1.salary < t2.salary
order by t1.salary desc, t2.salary

Demo on DB Fiddle:

name    | name
:------ | :---
John    | Tom 
William | John
William | Tom

Advertisement

Answer