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)?

Please note that without `order by` there is no clearly defined "first record" - it could be one of them today and another one tomorrow. — piet.t, May 16 '17 at 06:05
Classic example of a [greatest-n-per-group](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) query. — Clockwork-Muse, May 17 '17 at 19:32

Esperento57 · Accepted Answer · 2017-05-15T17:03:49.370

0

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

edited May 15 '17 at 17:03

answered May 15 '17 at 16:57

Esperento57

16,521
3
39
45

Wow, a very simple solution. I made an adaptation to your second alternative. I did not think of this before, thank you very much for the help – Zeca Tatu May 15 '17 at 17:10
@ZecaTatu - WARNING - the second example only works because the only data being returned are also the key columns. In most cases you'll have additional columns, and you won't be able to use it. – Clockwork-Muse May 17 '17 at 19:30
@Esperento57 - you're aware that the `OVER` clause accepts an `ORDER BY`, right? For that matter, depending on the exact version of DB2 used here, there's a `FIRST` function available too. – Clockwork-Muse May 17 '17 at 19:31
Yes i know. But un db2 order by are not obligatory AND if there are not order by , order are rrn of file AND in this ask, he want the first Line without order ... First function is a récent function, all system are not ... – Esperento57 May 18 '17 at 10:39

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

1 Answers1