aggregate(Equal to) to bit operator in sql?-CodePudding

I want to fetch data where Max(isPresent) == 1. isPresent is my bit column

SELECT EmpID FROM EMPtable where Max(isPresent) = 1

How can I Achieve this one.?

CodePudding user response：

I assume the problem is that there are multiple rows per employee in EMPtable in spite of its name, and so you can't simply say WHERE isPresent = 1 because it would return too many rows.

You can convert it to a different type first (but you need to group in some way, otherwise you'll still get multiple rows per employee):

SELECT EmpID FROM dbo.EMPtable 
GROUP BY EmpID
HAVING Max(CONVERT(tinyint,isPresent)) = 1;

Or:

SELECT EmpID FROM dbo.EMPtable AS e
  WHERE EXISTS (SELECT 1 FROM dbo.EMPtable
    WHERE EmpID = e.EmpID AND isPresent = 1)
GROUP BY EmpID;

Or:

WITH e AS (SELECT EmpID, pos = ROW_NUMBER() OVER
  (PARTITION BY EmpID ORDER BY EmpID)
  FROM dbo.EMPtable
  WHERE isPresent = 1)
SELECT EmpID FROM e WHERE pos = 1;

The method you use depends on what else you expect to return along with the EmpID. Here is an example fiddle.

Though this is largely a variation of greatest-n-per-group.

CodePudding user response：

Or perhaps something as simple as:

SELECT DISTINCT EmpID FROM EMPtable where isPresent = 1