sql top Come limitare il numero di righe restituite da una query Oracle dopo l'ordine?




select top 1 oracle (12)

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

più grande poi i valori lo scoprono

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

meno poi i valori lo scoprono

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

C'è un modo per far si che una query Oracle comporti come se contenga una clausola MySQL limit ?

In MySQL , posso farlo:

select * 
from sometable
order by name
limit 20,10

per ottenere il 21esimo alla 30a riga (salta il primo 20, dai il prossimo 10). Le righe vengono selezionate dopo l' order by , quindi inizia realmente sul nome XX in ordine alfabetico.

In Oracle , l'unica cosa che la gente menziona è la pseudo colonna di rownum , ma viene valutata prima order by , il che significa che:

select * 
from sometable
where rownum <= 10
order by name

restituirà un set casuale di dieci righe ordinate per nome, che di solito non è quello che voglio. Inoltre non consente di specificare un offset.


Meno istruzioni SELECT. Inoltre, meno consumo di prestazioni. Crediti a: [email protected]

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

Come sopra con le correzioni. Funziona ma sicuramente non è carina.

   WITH
    base AS
    (
        select *                   -- get the table
        from sometable
        order by name              -- in the desired order
    ),
    twenty AS
    (
        select *                   -- get the first 30 rows
        from base
        where rownum <= 30
        order by name              -- in the desired order
    )
    select *                       -- then get rows 21 .. 30
    from twenty
    where rownum < 20
    order by name                  -- in the desired order

Onestamente, meglio usare le risposte di cui sopra.


A partire da Oracle 12c R1 (12.1), esiste una clausola di limitazione delle righe . Non usa la sintassi LIMIT conosciuta, ma può fare il lavoro meglio con più opzioni. Puoi trovare la sintassi completa qui .

Per rispondere alla domanda originale, ecco la query:

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(Per versioni Oracle precedenti, fare riferimento ad altre risposte in questa domanda)

Esempi:

Gli esempi seguenti sono stati citati dalla pagina collegata , nella speranza di impedire il link rot.

Impostare

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

Cosa c'è nel tavolo?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

Ottieni le prime N righe

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

Prendi le prime N righe, se N una fila ha delle cravatte, prendi tutte le righe legate

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

Top x % di righe

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

Usando un offset, molto utile per l'impaginazione

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

È possibile combinare l'offset con le percentuali

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

Ho fatto alcuni test delle prestazioni per i seguenti approcci:

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

Analitico

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

Breve alternativa

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

risultati

La tabella aveva 10 milioni di record, l'ordinamento era su una riga datetime non indicizzata:

  • Spiega piano ha mostrato lo stesso valore per tutti e tre i seletti (323168)
  • Ma il vincitore è AskTom (con seguito analitico dietro)

La selezione delle prime 10 righe ha richiesto:

  • AskTom: 28-30 secondi
  • Analitico: 33-37 secondi
  • Breve alternativa: 110-140 secondi

Selezione di righe tra 100.000 e 100.010:

  • AskTom: 60 secondi
  • Analitico: 100 secondi

Selezione delle righe tra 9.000.000 e 9.000.010:

  • AskTom: 130 secondi
  • Analitico: 150 secondi

Se non si è su Oracle 12C, è possibile utilizzare la query TOP N come di seguito.

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

Puoi anche spostare questo da clausola in clausola come segue

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

Qui in realtà stiamo creando una vista in linea e rinominando rownum come rnum. Puoi utilizzare rnum nella query principale come criterio di filtro.


(non testato) qualcosa come questo potrebbe fare il lavoro

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

C'è anche il rango della funzione analitica, che puoi usare per ordinare.


Una soluzione analitica con una sola query nidificata:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() potrebbe essere sostituito da Row_Number() ma potrebbe restituire più record di quanto ci si aspetti se ci sono valori duplicati per nome.


Su Oracle 12c (vedere la clausola di limitazione delle righe in riferimento SQL ):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Le query di impaginazione con l'ordine sono davvero complicate in Oracle.

Oracle fornisce una pseudocolonna ROWNUM che restituisce un numero che indica l'ordine in cui il database seleziona la riga da una tabella o una serie di viste congiunte.

ROWNUM è una pseudocolonna che mette molte persone nei guai. Un valore ROWNUM non viene assegnato in modo permanente a una riga (questo è un malinteso comune). Può essere fonte di confusione quando viene assegnato un valore ROWNUM. Un valore ROWNUM viene assegnato a una riga dopo che ha superato i predicati di filtro della query ma prima di aggregazione o ordinamento di query .

Inoltre, un valore ROWNUM viene incrementato solo dopo che è stato assegnato.

Questo è il motivo per cui la seguente query non restituisce righe:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

La prima riga del risultato della query non passa il predicato ROWNUM> 1, quindi ROWNUM non aumenta a 2. Per questo motivo, nessun valore ROWNUM diventa maggiore di 1, di conseguenza, la query non restituisce righe.

La query correttamente definita dovrebbe assomigliare a questa:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

Scopri di più sulle query di impaginazione nei miei articoli sul blog di Vertabelo :


Per ogni riga restituita da una query, la pseudocolonna ROWNUM restituisce un numero che indica l'ordine in cui Oracle seleziona la riga da una tabella o un insieme di righe unite. La prima riga selezionata ha un ROWNUM di 1, il secondo ne ha 2 e così via.

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ROWNUM <= 100

L'ho implementato nel server oracle 11.2.0.1.0


Puoi usare una subquery per questo come

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Dai un'occhiata anche all'argomento su ROWNUM e ai risultati limitati su Oracle / AskTom per maggiori informazioni.

Aggiornamento : per limitare il risultato con entrambi i limiti inferiore e superiore le cose si gonfiano un po 'di più

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copiato dall'articolo AskTom specificato)

Aggiornamento 2 : a partire da Oracle 12c (12.1) è disponibile una sintassi per limitare le righe o iniziare con gli offset.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Vedi questa risposta per ulteriori esempi. Grazie a Krumia per il suggerimento.





limit