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;

I don't think there is any other elegant way to do it. There may be other ways to do it, but those will not necessarily be elegant anyways. — Anjan Biswas, Sep 18 '12 at 14:35
what are you trying to avoid with what you have? or what...... about the current query is "not elegant"? http://stackoverflow.com/questions/3451534/how-to-do-top-1-in-oracle — gloomy.penguin, Sep 18 '12 at 15:26

score 1 · Accepted Answer · answered Sep 18 '12 at 18:04

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.

enter image description here

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

How could you rewrite using analytical function? – Revious Sep 18 '12 at 19:50 — Revious, Sep 18 '12 at 19:50

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

1 Answers1