'Want SQL statement to return all columns

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)
sqlsql-server


Solution 1:[1]

SELECT LastName, FirstName, AnnualSalary
FROM Instructors
WHERE AnnualSalary NOT IN
(SELECT AnnualSalary
FROM Instructors
GROUP BY AnnualSalary
HAVING COUNT(AnnualSalary) > 1)
ORDER BY LastName, FirstName;

Solution 2:[2]

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

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Blastfurnace
Solution 2 RegBes
Solution 3

Related Questions