Want SQL statement to return all columns-CodePudding

Write a SELECT statement that returns the LastName, FirstName, and AnnualSalary columns of each instructor that has a unique annual salary. In other words, don’t include instructors that have the same annual salary as another instructor.

And the code that I tried is

SELECT DISTINCT
    LastName,
    FirstName,
    AnnualSalary
FROM
    Instructors i
WHERE
    i.AnnualSalary NOT IN (SELECT AnnualSalary
                           FROM Instructors i1
                           WHERE i.AnnualSalary <> i1.AnnualSalary)

CodePudding user response：

You were close, try with this change

SELECT 
    LastName,
    FirstName,
    AnnualSalary
FROM
    Instructors i
WHERE
    i.AnnualSalary NOT IN (SELECT AnnualSalary
                           FROM Instructors i1
                           group by AnnualSalary
                           having count(*) > 1)

i removed the distinct keyword as it is not required

CodePudding user response：

Here is your answer,

SELECT i1.* 
FROM Instructors i1
WHERE (SELECT count(1) 
       FROM Instructors i2 
       WHERE i1.AnnualSalary = i2.AnnualSalary and not (i1.FirstName = i2.FirstName and i1.LastName = i2.LastName) 
      )<>1

Hope you will got your answer.