Is there any clause similar to LIMIT in PL/SQL

Asked

Viewed 5,935 times

0

I am using PL/SQL and would like to know if you have any clause or sql script that is similar to sql LIMIT clause, I’ve been searching here and found rownum < = X , but does not meet what I want to do. I’ll try to explain

SELECT

select 
  codfil, coditprod, qtde 
from 
  mov_lotevencto
order by 
  qtde desc;

Upshot

  CODFIL         CODITPROD      QTDE
1 106            65303          6098,4
2 106            65303          5450,4
3 106            65303          3648,6
4 106            50635          3050,8
5 106            58973          2947,8

If I use rownum <= 1 it does not bring the first line of the result above it brings me that:

  CODFIL         CODITPROD      QTDE
1 106            56703          1,800

Doubt

It would have to bring only the first line, because I want to do a select that shows the lot that has the most items only the first line. If you can help me I appreciate.

sql oracle pl-sql

2 answers

5


The column ROWNUM, Oracle returns the line number inside the search result.

The number is assigned according to the order in which the rows are removed from the/sql table.

This order is defined before the ORDER BY, because this is done after the "resultset" is created.

For your SQL to work properly you have to do:

select * from (
  select 
    codfil, coditprod, qtde 
  from 
    mov_lotevencto
  order by 
    qtde desc
) where rownum <= 1;

Practical Example

I made a practical example

http://sqlfiddle.com/#! 4/407cf/4

  • Thanks Marlon tested here is worked out, it was just so I was trying, it would be much simpler if I had something like a LIMIT or a TOP, but all well worth the help.

  • @Isacoliveira to do what you want in Oracle only as above. In SQL Server you can use select top 1 * from tabelaand in Mysql select * from tabela where LIMIT 1

1

In mysql:

SELECT * FROM tabelaxpto LIMIT 1

In sql:

SELECT TOP 1* FROM tabelaxpto

In pl sql:

SELECT * FROM tabelaxpto WHERE rownum = 1

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.