sql - developer - ¿Cómo usar Oracle ORDER BY y ROWNUM correctamente?

rownum oracle

Question

Me está resultando difícil convertir los procedimientos almacenados de SQL Server a Oracle para que nuestro producto sea compatible.

Tengo consultas que devuelve el registro más reciente de algunas tablas, en función de una marca de tiempo:

Servidor SQL:

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

=> Eso me devolverá el registro más reciente

Pero Oracle:

SELECT *
FROM raceway_input_labo 
WHERE  rownum <= 1
ORDER BY t_stamp DESC

=> Eso me devolverá el registro más antiguo (probablemente dependiendo del índice), ¡independientemente de la instrucción ORDER BY !

Encapsulé la consulta de Oracle de esta manera para que coincida con mis requisitos:

SELECT * 
FROM 
    (SELECT *
     FROM raceway_input_labo 
     ORDER BY t_stamp DESC)
WHERE  rownum <= 1

y funciona. Pero me parece un hack horrible, especialmente si tengo muchos registros en las tablas involucradas.

Cuál es la mejor manera de lograr esto ?

Answer 1

Documentado un par de problemas de diseño con esto en un comentario anterior. En el cuento corto, en Oracle, debe limitar los resultados manualmente cuando tenga tablas y / o tablas grandes con los mismos nombres de columna (y no quiere explicitarlas explícitamente y cambiarles el nombre a todas). La solución fácil es descubrir su punto de corte y limitarlo en su consulta. O también puede hacer esto en la consulta interna si no tiene la restricción de nombres de columna conflictiva. P.ej

WHERE
m_api_log.created_date BETWEEN  TO_DATE (''10/23/2015 05:00'', ''MM/DD/YYYY HH24:MI'') AND  TO_DATE (''10/30/2015 23:59'', ''MM/DD/YYYY HH24:MI'')  

reducirá los resultados sustancialmente. Luego puede ORDENAR o incluso hacer la consulta externa para limitar las filas.

Además, creo que TOAD tiene una función para limitar las filas; pero no estoy seguro de que eso limite la consulta real en Oracle. No es seguro.

Answer 2

La instrucción where se ejecuta antes del order by . Por lo tanto, su consulta deseada es decir " tomar la primera fila y luego ordenarla por t_stamp desc ". Y eso no es lo que intentas.

El método de subconsulta es el método adecuado para hacer esto en Oracle.

Si desea una versión que funcione en ambos servidores, puede usar:

select ril.*
from (select ril.*, row_number() over (order by t_stamp desc) as seqnum
      from raceway_input_labo ril
     ) ril
where seqnum = 1

El exterior * devolverá "1" en la última columna. Debería enumerar las columnas individualmente para evitar esto.

Answer 3

Solo use Rownum como el siguiente

select *
from (select t.*
      from raceway_input_labo ril
      order by t_stamp desc
     )     
where rownum = 1

Answer 4

Una alternativa que sugeriría en este caso de uso es usar MAX (t_stamp) para obtener la última fila ... por ej.

select t.* from raceway_input_labo t
where t.t_stamp = (select max(t_stamp) from raceway_input_labo) 
limit 1

Mi preferencia de patrón de codificación (tal vez) - confiable, generalmente funciona igual o mejor que tratar de seleccionar la 1ª fila de una lista ordenada - también la intención es más explícitamente legible.
Espero que esto ayude ...

SQLer

Answer 5

Use ROW_NUMBER() lugar. ROWNUM es una pseudocolumna y ROW_NUMBER() es una función. Puede leer sobre la diferencia entre ellos y ver la diferencia en el resultado de las siguientes consultas:

SELECT * FROM (SELECT rownum, deptno, ename
           FROM scott.emp
        ORDER BY deptno
       )
 WHERE rownum <= 3
 /

ROWNUM    DEPTNO    ENAME
---------------------------
 7        10    CLARK
 14       10    MILLER
 9        10    KING


 SELECT * FROM 
 (
  SELECT deptno, ename
       , ROW_NUMBER() OVER (ORDER BY deptno) rno
  FROM scott.emp
 ORDER BY deptno
 )
WHERE rno <= 3
/

DEPTNO    ENAME    RNO
-------------------------
10    CLARK        1
10    MILLER       2
10    KING         3