CODEWITHSUNDEEP
oracle-databaseselect
Klaus Seidel
Klaus Seidel

Reputation: 63

Select last 12 rows oracle

I want to do a select in oracle that returns more or less 300 rows, but I just want the 12 last registries. Here is the query: 

(SELECT * 
FROM series 
ORDER BY odata DESC) estatisticas 
WHERE ponteiro = 50 AND lotus = 30

Upvotes: 6

Views: 58617

Answers (2)

Pablo
Pablo

Reputation: 11041

The Oracle RDBMS uses a pseudo-column called rownum when constructing the result set of a query. Each row in the result is numbered in ascending order, starting from 0. You can evaluate conditions as follows:

select job_name from dba_scheduler_jobs where rownum < 10;

This will return the first 10 rows it finds.

It is important to remember that the rownum is evaluated after the records have been fetched from the database but before the order by clauses in the query.

so, your query should look as follows:

SELECT * FROM 
((SELECT * FROM series 
    ORDER BY odata DESC) estatisticas 
    WHERE ponteiro = 50 AND lotus = 30 [ASC/DESC]) 
WHERE rownum < 12;

You should set the order by clause to ascending or descending, depending on which rows you want to get.

Upvotes: 1

Chris
Chris

Reputation: 4083

Something along the lines of:

select * from 
    ( select estatisticas, rownum rn
        (SELECT *
        FROM series 
        ORDER BY odata DESC) estatisticas 
        WHERE ponteiro = 50 AND lotus = 30
    order by odata asc) where rownum <=12

Edit: updated it for your query, you want to sort it opposite of the inner query, ascending in your case, so you can get the last 12

Upvotes: 10

Related Questions