1. Registered User
Join Date
Mar 2012
Posts
4

Table T1
C1 C2 C3 C4 C5.............
--------------------------------------
A 1 N
B 1 Y
B 1 N
B 2 N

I have this above table.
For every entry with C3 = 'Y', there will be a C3 = 'N'.
I want to write a query which selects all the records ignoring the records with C3 = 'N' when there is an entry for 'Y'.

Expected RESULT:

C1 C2 C3 C4 C5.............
--------------------------------------
A 1 N
B 1 Y
B 2 N

I am using a DB2 database.

Thanks,
Raja

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
My question is what values did you want for C4, C5, so on...

If I ignore those other columns and only focused on C1, C2 and C3,
it would be easy by grouping C1 and C2 then take max of C3, like...
Code:
```SELECT c1 , c2
, MAX(c3) AS c3
FROM  t1
GROUP BY
c1 , c2```
So, please publish more sample data(rows and columns) including normal and exceptional data and expected results from the data,
if you want to see more close response for your requirements.

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Another example which considering other columns might be like...

Code:
```SELECT c1 , c2 , c3 , c4 , c5 , ...
FROM (SELECT t1.*
, ROW_NUMBER() OVER(PARTITION BY c1 , c2
ORDER BY c3 DESC) AS rn
FROM  t1
)
WHERE rn = 1
;```

4. Registered User
Join Date
Mar 2012
Posts
4
Hi,

Thanks much for your help. But the max(c3) as C3 is not working.
I tried to give more inputs on this. Please check

C1 C2 C3 C4 C5.............
--------------------------------------
A 1 N
B 1 Y
B 1 N
B 2 N
C 1 N
C 4 Y
C 4 N
D 7 N
E 6 Y
E 6 N
_________________________

Hope this will do...

5. Registered User
Join Date
Mar 2012
Posts
4
Desired output

C1 C2 C3 C4 C5.............
--------------------------------------
A 1 N
B 1 Y
B 2 N
C 1 N
C 4 Y
D 7 N
E 6 Y
_________________________

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
The result of my trial was here.
Code:
```------------------------------ Commands Entered ------------------------------
WITH
T1(c1 , c2 , c3) AS (
VALUES
( 'A' , 1 , 'N' )
, ( 'B' , 1 , 'Y' )
, ( 'B' , 1 , 'N' )
, ( 'B' , 2 , 'N' )
, ( 'C' , 1 , 'N' )
, ( 'C' , 4 , 'Y' )
, ( 'C' , 4 , 'N' )
, ( 'D' , 7 , 'N' )
, ( 'E' , 6 , 'Y' )
, ( 'E' , 6 , 'N' )
)
SELECT c1 , c2
, MAX(c3) AS c3
FROM  t1
GROUP BY
c1 , c2
;
------------------------------------------------------------------------------

C1 C2          C3
-- ----------- --
A            1 N
B            1 Y
B            2 N
C            1 N
C            4 Y
D            7 N
E            6 Y

7 record(s) selected.```
Isn't it what you wanted?
I want to write a query which selects all the records ignoring the records with C3 = 'N' when there is an entry for 'Y'.
If the result of me was not your required result,

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Or, change datatype of C2 to varchar, like...
Code:
```------------------------------ Commands Entered ------------------------------
WITH
T1(c1 , c2 , c3) AS (
VALUES
( 'A' , '1' , 'N' )
, ( 'B' , '1' , 'Y' )
, ( 'B' , '1' , 'N' )
, ( 'B' , '2' , 'N' )
, ( 'C' , '1' , 'N' )
, ( 'C' , '4' , 'Y' )
, ( 'C' , '4' , 'N' )
, ( 'D' , '7' , 'N' )
, ( 'E' , '6' , 'Y' )
, ( 'E' , '6' , 'N' )
)
SELECT c1 , c2
, MAX(c3) AS c3
FROM  t1
GROUP BY
c1 , c2
;
------------------------------------------------------------------------------

C1 C2 C3
-- -- --
A  1  N
B  1  Y
B  2  N
C  1  N
C  4  Y
D  7  N
E  6  Y

7 record(s) selected.```
But, the values of C3 were same as my previous result and your expected result.

8. Registered User
Join Date
Mar 2012
Posts
4

## Thanks you

Thanks very much...
I see what you are saying now...
Its working

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•