How to select only the first record from a recordset in DB2?

Question

How to select only the first record from a recordset in Db2?

Example, I made the query below:

SELECT NU_DOC_CLIENTE_DIT,                                  
       NU_DOC_CLI_IND_CID,                                  
       TS_OPERACAO_CID                                      
FROM CLIENTE
WHERE NU_DOC_CLI_IND_CID IN                                       
                  (SELECT NU_DOC_CLI_IND_CID                      
                     FROM CLIENTE
                    WHERE CO_USERIDOPER_CID = X'0000000000000000')
GROUP BY NU_DOC_CLIENTE_DIT,                                      
         NU_DOC_CLI_IND_CID,                                      
         TS_OPERACAO_CID;        

Results in:

---------+---------+---------+---------+---------+---------+------
NU_DOC_CLIENTE_DIT  NU_DOC_CLI_IND_CID  TS_OPERACAO_CID           
---------+---------+---------+---------+---------+---------+------
                 1                   0  2016-10-06-10.17.38.000000
                 1                   0  2016-10-06-11.08.10.000000
                 1                   0  2016-10-06-11.11.12.000000
                 1                   0  2016-10-21-11.20.51.000000
                 1                   0  2016-10-21-14.08.52.000000
                 2                   1  2015-03-06-13.41.00.000000
                 2                   1  2016-10-06-09.55.39.000000
                 2                   1  2016-10-18-14.32.00.000000
                 3                   2  2016-10-18-14.35.11.000000
                 3                   2  2016-10-18-14.36.28.000000
                 3                   2  2016-10-18-14.43.45.000000

I would like to have the result:

---------+---------+---------+---------+---------+---------+------
NU_DOC_CLIENTE_DIT  NU_DOC_CLI_IND_CID  TS_OPERACAO_CID           
---------+---------+---------+---------+---------+---------+------
                 1                   0  2016-10-06-10.17.38.000000
                 2                   1  2015-03-06-13.41.00.000000
                 3                   2  2016-10-18-14.35.11.000000

I can not do with "FETCH FIRST 1 ROWS ONLY" because it brings me the unwanted result:

---------+---------+---------+---------+---------+---------+------
NU_DOC_CLIENTE_DIT  NU_DOC_CLI_IND_CID  TS_OPERACAO_CID           
---------+---------+---------+---------+---------+---------+------
                 1                   0  2016-10-06-10.17.38.000000

How to get the result of only one row per key group (NU_DOC_CLIENTE_DIT, NU_DOC_CLI_IND_CID, TS_OPERACAO_CID)?

Answer 1

if you want really first without sort :

select * from (
   SELECT NU_DOC_CLIENTE_DIT, NU_DOC_CLI_IND_CID, TS_OPERACAO_CID, 
   rownumber() over(partition by NU_DOC_CLIENTE_DIT, NU_DOC_CLI_IND_CID) rang 
   FROM CLIENTE
   WHERE CO_USERIDOPER_CID =X'0000000000000000'
) tmp where rang=1

or otherwise simply

SELECT NU_DOC_CLIENTE_DIT,                                  
   NU_DOC_CLI_IND_CID,                                  
   min(TS_OPERACAO_CID) TS_OPERACAO_CID                                     
FROM CLIENTE
WHERE CO_USERIDOPER_CID = X'0000000000000000'
GROUP BY NU_DOC_CLIENTE_DIT, NU_DOC_CLI_IND_CID