0

I have the below table

CREATE TABLE result(
   id    INTEGER  NOT NULL 
  ,cid   INTEGER  NOT NULL
  ,aid   INTEGER  NOT NULL
  ,agid  INTEGER  NOT NULL
  ,ft    INTEGER  NOT NULL
  ,ct    INTEGER  NOT NULL
  ,jid   INTEGER  NOT NULL
  ,fcn   INTEGER  NOT NULL
  ,iap   INTEGER  NOT NULL
  ,flags INTEGER  NOT NULL
  ,afcf  INTEGER  NOT NULL
);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (44,2,9,6,2,1698222904,52,-1,1,65536,65539);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (44,2,9,6,2,1698222904,52,-1,2,65536,4099);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (54,2,9,6,2,1698223114,56,-1,1,65536,65539);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (54,2,9,6,2,1698223114,56,-1,2,65536,4099);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (68,2,9,6,2,1698223386,59,-1,1,65536,3);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (68,2,9,6,2,1698223386,59,-1,2,65536,4099);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (80,2,9,6,2,1698223477,60,-1,1,65536,3);
INSERT INTO result(id,cid,aid,agid,ft,ct,jid,fcn,iap,flags,afcf) VALUES (80,2,9,6,2,1698223477,60,-1,2,65536,4099);

The values looks like

id cid aid agid ft ct jid fcn iap flags afcf
44 2 9 6 2 1698222904 52 -1 1 65536 65539
44 2 9 6 2 1698222904 52 -1 2 65536 4099
54 2 9 6 2 1698223114 56 -1 1 65536 65539
54 2 9 6 2 1698223114 56 -1 2 65536 4099
68 2 9 6 2 1698223386 59 -1 1 65536 3
68 2 9 6 2 1698223386 59 -1 2 65536 4099
80 2 9 6 2 1698223477 60 -1 1 65536 3
80 2 9 6 2 1698223477 60 -1 2 65536 4099

In the above table for each of the group iap whose value here is [1,2]. I want to get that iap where afcf & 65536 = 0 else I want the value to be NULL.

So my output would contain 2 for above table as all the rows in iap 2 satisfy the condition `afcf & 65536 = 0.

What needs to be done

For each of the group of iap, check if all its rows satisfies the condition afcf & 65536 = 0 if yes then print that group id iap.

Expected Output

Since iap = [2] satisfied above condition for all of its rows

2

This is what I tried, which is wrong.

SELECT iap FROM result
GROUP BY iap
HAVING afcf & 65536 = 0
7
  • 2
    The test data in your question has duplicate primary key values.
    – Dan Guzman
    Nov 2, 2023 at 10:10
  • HAVING afcf & 65536 = 0 ??? You do not apply aggregate function. 4 rows in a group (GROUP BY iap) have different values in this column (65539 AND 3) - what of them you want to be tested with your condition?
    – Akina
    Nov 2, 2023 at 10:12
  • @Akina i told you the query is wrong, I want to do something like above which gives the group id iap Nov 2, 2023 at 11:34
  • @DanGuzman that was a mistake, edited! Nov 2, 2023 at 11:36
  • I cannot understand your question completely. Show desired output for shown data, and add detailed explanations.
    – Akina
    Nov 2, 2023 at 12:24

1 Answer 1

0

my output would contain 2 for above table as all the rows in iap 2 satisfy the condition afcf & 65536 = 0.

For example, this may be

SELECT iap 
FROM result
GROUP BY iap
HAVING MAX(afcf & 65536) = 0

But I'd use

SELECT iap 
FROM result
GROUP BY iap
HAVING MAX(afcf) < 65536

https://dbfiddle.uk/zPGhVZMY

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.