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 in Database by Kithuzzz
• 12,240 points
40 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 by narikkadan
• 20,880 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 in Database by Vaani
• 7,020 points
208 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 in Database by gaurav
• 18,960 points
75 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,280 points
360 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
325 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 in Database by Neha
• 8,920 points
154 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 in Database by Neha
• 8,920 points
3,209 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 in Database by narikkadan
• 20,880 points
36 views
0 votes
1 answer

Why do I need to configure the SQL dialect of a data source?

Dialect is defined as "a language's variety." ...READ MORE

answered Sep 18 in Database by narikkadan
• 20,880 points
21 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