Comment utiliser correctement Oracle ORDER BY et ROWNUM ?

Question

J'ai du mal à convertir les procédures stockées de SQL Server à Oracle pour que notre produit soit compatible avec ce dernier.

J'ai des requêtes qui renvoient l'enregistrement le plus récent de certaines tables, sur la base d'un horodatage :

SQL Server :

SELECT TOP 1 *
FROM RACEWAY_INPUT_LABO
ORDER BY t_stamp DESC

\=> Cela me renvoie l'enregistrement le plus récent

Mais Oracle :

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

\=> Cela me renverra l'enregistrement le plus ancien (probablement en fonction de l'index), quel que soit le numéro de l'enregistrement. ORDER BY déclaration !

J'ai encapsulé la requête Oracle de cette façon pour répondre à mes besoins :

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

et ça marche. Mais cela me semble être un horrible hack, surtout si j'ai beaucoup d'enregistrements dans les tables concernées.

Quelle est la meilleure façon d'y parvenir ?

Answer 1

Le site where l'instruction est exécutée avant le site order by . Donc, votre requête souhaitée est de dire " prendre la première ligne et ensuite l'ordonner par t_stamp desc ". Et ce n'est pas ce que vous voulez dire.

La méthode des sous-requêtes est la méthode appropriée pour faire cela dans Oracle.

Si vous voulez une version qui fonctionne dans les deux serveurs, vous pouvez utiliser :

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

L'extérieur * retournera "1" dans la dernière colonne. Pour éviter cela, vous devez lister les colonnes individuellement.

Answer 2

Utilisez ROW_NUMBER() à la place. ROWNUM est une pseudo-colonne et ROW_NUMBER() est une fonction. Vous pouvez lire la différence entre les deux et voir la différence dans les résultats des requêtes ci-dessous :

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

Answer 3

Depuis Oracle 12c, nous avons maintenant clauses limitant les rangs qui font exactement cela.

SELECT *
FROM raceway_input_labo 
ORDER BY t_stamp DESC
FETCH FIRST ROW ONLY

Ou beaucoup alternatives pour différents scénarios (n premières lignes, traitement des égalités, etc.).

Answer 4

J'ai documenté quelques problèmes de conception dans un commentaire ci-dessus. En bref, dans Oracle, vous devez limiter les résultats manuellement lorsque vous avez de grandes tables et/ou des tables avec les mêmes noms de colonnes (et vous ne voulez pas les taper explicitement et les renommer toutes). La solution la plus simple est de déterminer votre point de rupture et de le limiter dans votre requête. Vous pouvez également le faire dans la requête interne si vous n'avez pas de contrainte de conflits de noms de colonnes. Par exemple

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')  

réduira considérablement les résultats. Ensuite, vous pouvez utiliser ORDER BY ou même faire la requête externe pour limiter les lignes.

De plus, je pense que TOAD dispose d'une fonction permettant de limiter les lignes, mais je ne suis pas sûr qu'elle permette de limiter la requête dans Oracle. Je ne suis pas sûr.

Answer 5

Une alternative que je suggère dans ce cas d'utilisation est d'utiliser le MAX(t_stamp) pour obtenir la dernière ligne ... ex.

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

Ma préférence pour le modèle de codage (peut-être) - fiable, il donne généralement des résultats équivalents ou supérieurs à ceux obtenus en essayant de sélectionner la première ligne d'une liste triée - l'intention est également plus explicitement lisible.
J'espère que cela vous aidera ...

SQLer