CODEWITHSUNDEEP
phpmysqlsql
seus
seus

Reputation: 568

Comparing two values in the same table

I have a table which I would like get all the names of someone who's salary is less than then the next person in the same table, I have tried this but its not working any suggestions?

select t1.Name, t2.Name as Name2 
from employees t1 
inner join employees t2 on 
t1.ID = t2.ID
where t1.Salary < t2.Salary;

I am trying to print out the names of each person that has a lesser salary then the next for example 

Joe "has less then" Bob
Joe "has less then" foo
Joe "has less then" Bar
Joe "has less then" Pete

Upvotes: 0

Views: 462

Answers (4)

Ketu
Ketu

Reputation: 1718

select t1.Name || " has less then " || t2.Name from employees t1, employees t2 where t1.Salary < t2.Salary;

if you remove the inner join (resulting join will be a cross join) in that (I believe) you will be able to find what you are looking for.

PS: the query is not tested on any database schema, however it will work, m not sure on the concatenation syntax though.

Upvotes: 0

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

Compare your current and next id column

select t1.Name, t2.Name as Name2 ,t1.salary,t2.salary
from employees t1 
inner join employees t2 on 
t1.ID+1 = t2.Id
where t1.Salary < t2.Salary;

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

Try this:

select t.LastName
from (
    select Name, 
    Salary, 
    @last_name AS LastName, 
    @last_sal AS LastSal, 
    @last_name := Name , 
    @last_sal := Salary 
    from employees
    cross join (select @last_name := NULL, @last_sal := NULL) param
    ) t
where t.LastSal < t.Salary;

Upvotes: 0

L Kefalas
L Kefalas

Reputation: 63

You are joining on the same ID. You are comparing the salary of the same person.

Upvotes: 0

Related Questions