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