Limit the number of oracle records

Navigation
0

I use oracle 11, I have the following query: 

select * from(select "Loss","Date" from TEST order by "Loss" desc) where rownum < 20

This returns 20 records that you have the largest "Loss", is there another way to do it?

My question is in the next query: 

select * from(
SELECT EXTRACT(YEAR FROM "Date") AS years, Count("Loss") AS events, Max("Loss") 
AS max_losses, Sum("Loss") AS sum_losses 
FROM Test GROUP BY EXTRACT(YEAR FROM "Date") 
ORDER BY EXTRACT(YEAR FROM "Date")
) where rownum < 20

I would have to give something like this: 

2017 5  3210 500581.25 
2016 15 8900 1658226.32

But he's giving me 

2017 233 3210 500581.25 
2016 675 8900 1658226.32

How can I get the 20 records that have the most loss in that query?

    
asked by sirdaiz 20.06.2017 в 13:10
source

2 answers

2

You have to make a query of your query limiting with rownum 

SELECT * FROM 
    (SELECT EXTRACT(YEAR FROM "Date") AS years, Count("Loss") AS events, Max("Loss") 
    AS max_losses, Sum("Loss") AS sum_losses 
    FROM Test GROUP BY  EXTRACT(YEAR FROM "Date")
    ORDER BY EXTRACT(YEAR FROM "Date") 
) WHERE ROWNUM <= 20
    
answered by 20.06.2017 / 13:21
source
0

As of Oracle 12c R1 (12.1) there is a build to limit the number of results, the Row limiting clause . It's a bit more complex than LIMIT of MySQL, but it works.

To get the first 20 records, the appropriate clause would be FETCH FIRST 20 ROWS ONLY , at the end of the query.

    
answered by 20.06.2017 в 13:24
How to enter an input when clicking on a td with jquery or javascript? [closed] How to use a variable that returns a return