Geek asks and answers

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

Suppose we want to take the first 1 record of a result set. Is there a more elegant way to do this?

   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;
+5
source share
1 answer

I think your decision is ok. The only other solution with Oracle is to use an analytic function row_number(), but this makes it less elegant. Other databases have an operator TOP 1, but there is no other Oracle equivalent for it except ROWNUMoutside the subquery when used ORDER BY. I agree to use WITH, which makes it more readable. The following may be written faster, but I'm 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

, Oracle SQL ROWNUM top-N .

enter image description here

Oracle® Database SQL 11g 2 (11.2) E26088-01

+1

More articles:


All Articles