Oracle: An elegant way to take the first n record (top-k query)

Question

Let's assume we want to take the first 1 record of a resultset. Is there a more elegant way to do it?

   WITH temp
        AS (  SELECT WKFC_CRONOLOGIA_ID
                FROM SIUWKF.WKF_CRONOLOGIA c
               WHERE     Ogg_oggetto_id = vOGG_ID
                     AND TOG_TIPO_OGGETTO_ID = vTOG
                     AND C.WKFC_DATA_FIN = TO_DATE ('31/12/9999', 'DD/MM/YYYY')
                     AND Wkfc_Tipo = 'STATO'
            ORDER BY WKFC_DATA_INI DESC)
   SELECT WKFC_CRONOLOGIA_ID
     INTO vCRONOLOGIA_ID
     FROM temp
    WHERE ROWNUM = 1;

Answer 1

I think your solution is alright. The only other solution with Oracle is to use the row_number() analytical function but this makes it less elegant. Other databases have the TOP 1 statement but there is no other Oracle equivalent to it than ROWNUM outside a subquery when you have an ORDER BY in use. I agree to use WITH which makes it more readable. The following might be written faster but I am not sure if it is more elegant. Maybe a matter of taste:

SELECT * FROM
(  SELECT WKFC_CRONOLOGIA_ID
                FROM SIUWKF.WKF_CRONOLOGIA c
               WHERE     Ogg_oggetto_id = vOGG_ID
                     AND TOG_TIPO_OGGETTO_ID = vTOG
                     AND C.WKFC_DATA_FIN = TO_DATE ('31/12/9999', 'DD/MM/YYYY')
                     AND Wkfc_Tipo = 'STATO'
            ORDER BY WKFC_DATA_INI DESC)
WHERE ROWNUM = 1

This is what Oracle SQL manual says about ROWNUM and top-N reporting and confirms your way in doing it.

Source Oracle® Database SQL Language Reference 11g Release 2 (11.2) E26088-01