How do I limit the number of rows returned by an Oracle query after ordering

0 votes

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

This is how I did this In MySQL, :

select * 
from sometable
order by name
limit 20,10

to reach rows 21 through 30 (skip the first 20, give the next 10). Since the rows are chosen after the order by, it actually begins with the 20th name in alphabetical order.

The rownum pseudo-column in Oracle is all that is mentioned, but it is evaluated before order by, which means that:

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

Will give me ten rows arranged in a random order, which is not what I want most of the time. In addition, no offset can be specified. 

Can someone please help me with this?

Sep 13, 2022 in Database by Kithuzzz
• 38,020 points
2,122 views

1 answer to this question.

0 votes

Use a subquery for this like:

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

I hope this helps you.

answered Sep 15, 2022 by narikkadan
• 63,680 points

Related Questions In Database

0 votes
1 answer

Number of rows affected by an UPDATE in PL/SQL

You can try using the following query: SELECT ...READ MORE

answered Feb 14, 2022 in Database by Vaani
• 7,070 points
1,302 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How do I change the menu language of Excel Online?

Click on App launcher from any worksheet ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
13,966 views
0 votes
1 answer

Automating Oracle script with nolio

Depending upon the details of your script ...READ MORE

answered Jul 17, 2018 in Other DevOps Questions by ajs3033
• 7,300 points
1,007 views
0 votes
1 answer

Is there any boolean type in Oracle database?

Nope. I don't think there is one But ...READ MORE

answered Oct 10, 2018 in Database by Neha
• 6,300 points
965 views
0 votes
1 answer

Please name some online websites to compile and run PL/SQL?

For executing Oracle SQL queries and PL/SQL ...READ MORE

answered Feb 11, 2022 in Database by Neha
• 9,020 points
1,408 views
0 votes
1 answer

ORACLE LIVE SQL IMPORT CSV DATA (live oracle sql)

Convert CSV to SQL http://www.convertcsv.com/csv-to-sql.htm This programme may be ...READ MORE

answered Feb 11, 2022 in Database by Neha
• 9,020 points
8,125 views
0 votes
1 answer

How do I run an sql query in php?

Try this: <?php $conn = new mysqli('localhost', 'jaydeep_mor', 'jaydeep_mor', ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,680 points
784 views
0 votes
1 answer

How do I perform the SQL Join equivalent in MongoDB?

The majority of the responses to this ...READ MORE

answered Sep 15, 2022 in Database by narikkadan
• 63,680 points
1,287 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP