CODEWITHSUNDEEP
sqlsql-server
user2097335
user2097335

Reputation: 65

how to find unique value from table

below is my table called companies

company_id        Status
1001                OFF
1002                OFF
1003                OFF
1003                ON

I need to avoid if any company_id with status 'ON'

I need out put like below

company_id        Status
1001                OFF
1002                OFF

How i write query for this

Upvotes: 1

Views: 59

Answers (3)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

SELECT company_ID, MAX(Status) AS Status FROM companies
GROUP BY company_ID
HAVING MAX(Status) = 'OFF'

You can use MIN and MAX functions to sort string values. In this case MAX will return ON before OFF.

Upvotes: -1

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9188

SELECT Company_Id, Status FROM Companies C 
WHERE NOT EXISTS(SELECT * FROM Companies 
WHERE Company_Id = C.Company_id AND Status = 'ON')

Upvotes: 3

John Woo
John Woo

Reputation: 263933

SELECT  *
FROM    tableName
WHERE   company_ID NOT IN
        (
            SELECT company_ID 
            FROM    tableName
            WHERE   Status = 'ON'
        )

or by using LEFT JOIN

SELECT  a.*
FROM    tableName a
        LEFT JOIN
        (
            SELECT company_ID 
            FROM    tableName
            WHERE   Status = 'ON'
        ) b ON a.company_ID = b.company_ID
WHERE   b.company_ID IS NULL

Upvotes: 3

Related Questions