Db2 SQL: Select statement for merging multiple rows

Question

I have the following data in my table:

GRANTEE	TTNAME	INSERTALLOWED	DELETEALLOWED	UPDATEALLOWED	SELALLOWED	TIMESTAMP
USER1	TABLE_A	Y		Y	Y	2023-01-01
USER1	TABLE_A		Y			2023-01-02

I want a SQL select statement where I can merge these two rows into one result, as I am interested in the permission a grantee has for a table name(TTNAME). The timestamp column should not matter/be ignored.

I want to have the following result:

GRANTEE	TTNAME	INSERTALLOWED	DELETEALLOWED	UPDATEALLOWED	SELALLOWED
USER1	TABLE_A	Y	Y	Y	Y

I tried several selects, non of them worked. I always get the result as 2 separated rows. I assume because they have a different timestamp!?

I got:

GRANTEE	TTNAME	INSERTALLOWED	DELETEALLOWED	UPDATEALLOWED	SELALLOWED
USER1	TABLE_A	Y		Y	Y
USER1	TABLE_A		Y

I tried the following statements:

1. using Group by:

SELECT GRANTEE, TTNAME, INSERTALLOWED, DELETEALLOWED, UPDATEALLOWED, SELALLOWED
FROM AUTHTABLE
WHERE GRANTEE='USER1' AND TTNAME='TABLE_A'
GROUP BY GRANTEE, TTNAME, INSERTALLOWED, DELETEALLOWED, UPDATEALLOWED, SELALLOWED;

2. Using a self join:

SELECT A.GRANTEE, A.TTNAME, A.INSERTALLOWED, A.DELETEALLOWED, A.UPDATEALLOWED, A.SELALLOWED
FROM AUTHTABLE AS A
INNER JOIN AUTHTABLE AS B
ON A.GRANTEE=B.GRANTEE AND A.TTNAME=B.TTNAME
AND A.TIMESTAMP<=B.TIMESTAMP
WHERE A.GRANTEE='USER1' AND A.TTNAME='TABLE_A';

Is there a way to merge these two rows into one (by disregarding the timestamp)?

Any help appreciated. Thanks.

Answer 1

The following SQL is working:

SELECT GRANTEE, TTNAME, 
MAX(INSERTALLOWED) as INSERTALLOWED, 
MAX(DELETEALLOWED) as DELETEALLOWED,
MAX(UPDATEALLOWED) AS UPDATEALLOWED,
MAX(SELALLOWED) as SELALLOWED 
FROM AUTHTABLE 
WHERE GRANTEE='USER1' AND ttname='TABLE_A' 
GROUP BY GRANTEE, TTNAME;