Fetch first n rows in db2 and rownum in oracle

Source: Internet
Author: User

Fetch first n rows in db2 and rownum in oracle if you want to obtain the first n rows in db2, you only need to add fetch first n rows only, but there is no fetch in oracle, many people on the Internet say that oracle's rownum <= n can be used to replace db2's fetch first n rows only, it is incorrect to obtain the first n rows after order by is required for the result set. According to my experiments, the rownum sequence seems to correspond to rowid, while the rowid sequence is related to the data sequence in the inserted Table (I don't know if the real implementation mechanism of oracle is like this, if you have time to study this oracle book system ). See the following oracle instances: www.2cto.com SQL> select rownum, id, age, name from loaddata; rownum id age name ------- ------ 1 200001 22 AAA 2 200002 22 BBB 3 200003 22 CCC 4 200004 22 DDD 5 200005 22 EEE 6 200006 22 AAASQL> select rownum, id, age, name from loaddata order by name; rownum id age name ------- ------ 1 200001 22 AAA 6 200006 22 AAA 2 200002 22 BBB 3 200003 22 CCC 4 200004 22 DDD 5 2 00005 22. Therefore, if you want to sort the first few rows, you can use the subquery select rownum, id, age, name from (select * from loaddata order by name ); however, I think that we should use the standard SQL function row_number () over () instead of the specific oracle function rownum (). You can write select id, age, name from (select row_number () over (order by name) as row_number, id, age, name from loaddata) where row_number <n; in this case, you do not need to consider whether it is in db2 or oracle.

Related Article

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.