search
HomeDatabaseOracleHow to query the first 10 records in oracle

In Oracle, you can use the where clause with rownum to query the first ten records. The where clause is used to limit the conditions of the query, and rownum is used to limit the total number of rows returned for the query. The syntax is "select * from table Name where rownum

How to query the first 10 records in oracle

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query the top 10 records in Oracle

How to query the top 10 records in the table in Oracle?

  select *
  from test
  where rownum <=10

The following is an introduction to rownum

The use of Rownum and row_number() over()

ROWNUM is a pseudo column provided by Oracle starting from 8. It is a SQL The results are numbered, always starting from 1. A common use is to output in pages.

For example,

  SELECT *
  FROM torderdetail a
  WHERE ROWNUM <= 10

This statement is to output the first 10 records. The purpose here is similar to The top of sql sever, but rownum should be said to be more powerful for the output of the specified number range

  SELECT *
  FROM (SELECT a.*, ROWNUM rn
  FROM torderdetail a)
  WHERE rn >= 10 AND rn <= 20

This statement outputs the 10th to 20th records. The reason why rownum rn is used here is to convert rownum into Example, because rownum itself can only use the = comparison can be done.

In practical applications, it is often required to get the most recent records. This requires sorting the records first and then getting rownum

Generally common

  SELECT *
  FROM (SELECT a.*
  FROM torderdetail a
  ORDER BY order_date DESC)
  WHERE ROWNUM <= 10

There has been a discussion in CSDN. Regarding the 10 closest records, someone gave such a statement

  SELECT a.*
  FROM torderdetail a
  WHERE ROWNUM <= 10
  ORDER BY order_date DESC

The reason why such a statement appears is mainly from the perspective of efficiency. The previous statement , it is necessary to scan the entire table and then sort, and then fetch 10 records. The latter statement will not scan the entire table, but will only fetch 10 records. Obviously, the efficiency of the latter statement will be much higher.

Then why is there a dispute? The dispute lies in the order of execution. Should the sorting be performed first to get 10 records, or should the 10 records be taken and then sorted? The results of fetching in the two orders are completely opposite. Sorting first and then fetching 10 records will fetch the 10 most recent records. Fetching 10 records first and then sorting will fetch the earliest 10 records. For this statement, it is generally believed that the execution order is to first fetch 10 records and then sort them. So this statement should be an error. But this is not actually the case. The execution order of this statement is related to the field of order by. If the field of your order by is pk, it is sorted first, and then 10 items are taken (faster than the first statement), and the sorting field When it is not PK, 10 items are taken first and then sorted. At this time, the result is different from the requirements, so the second way of writing must ensure that the result is correct when the sorting field is the primary key.

The analysis function Row_number() over() has been provided since 9I. Its general purpose is similar to rownum.

The general writing method row_number() over(order by order_date desc) generates the same order as the rownum statement, and the efficiency is the same (for the rownum statement that also has order by), so in this case both The usage is the same.

For grouping, taking the latest 10 records cannot be achieved by rownum. At this time, only row_number can be achieved. row_number() over(partition by grouping field order by sorting field) can be achieved after grouping. Number, for example, if you want to get the last 10 order records of each day in the past month

  SELECT *
  FROM (SELECT a.*,
  ROW_NUMBER () OVER (PARTITION BY TRUNC (order_date) ORDER BY order_date DESC)
  rn
  FROM torderdetail a)
  WHERE rn <= 10

Alternative usage of Rownum, sometimes we will encounter this demand, requiring the output of all the days of the month, many people will be troubled, There is no such table in the database, so how can we output all the days in a month? It can be solved with rownum:

  SELECT TRUNC (SYSDATE, &#39;MM&#39;) + ROWNUM - 1
  FROM DUAL
  CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), &#39;dd&#39;))

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query the first 10 records in oracle. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Oracle's Customer Base: Industries and ApplicationsOracle's Customer Base: Industries and ApplicationsApr 12, 2025 am 12:04 AM

Oracle has a wide and diverse customer base, covering multiple fields such as financial services, healthcare, retail and e-commerce, and manufacturing. 1) In financial services, Oracle FLEXCUBE improves operational efficiency and data security; 2) In health care, OracleHealth Sciences Clinical Development accelerates new drug research and development; 3) In retail and e-commerce, OracleRetail and OracleCDP achieve personalized customer experience; 4) In manufacturing, OracleERPCloud optimizes supply chain management.

How to use triggers for oracleHow to use triggers for oracleApr 11, 2025 pm 11:57 PM

Triggers in Oracle are stored procedures used to automatically perform operations after a specific event (insert, update, or delete). They are used in a variety of scenarios, including data verification, auditing, and data maintenance. When creating a trigger, you need to specify the trigger name, association table, trigger event, and trigger time. There are two types of triggers: the BEFORE trigger is fired before the operation, and the AFTER trigger is fired after the operation. For example, the BEFORE INSERT trigger ensures that the age column of the inserted row is not negative.

How to change the oracle table nameHow to change the oracle table nameApr 11, 2025 pm 11:54 PM

Two ways to rename Oracle table names: use SQL statements: ALTER TABLE <Old table name> RENAME TO <New table name>;Use PL/SQL statements: EXECUTE IMMEDIATE 'ALTER TABLE ' || :old_table_name || ' RENAME TO ' || :new_table_name;

How to return after oracle submittedHow to return after oracle submittedApr 11, 2025 pm 11:51 PM

Oracle provides the following ways to fall back on committed database changes: Use the ROLLBACK statement to immediately revoke all uncommitted changes. Operation through the database management tool interface. Use Oracle Flashback technology to return to a specific point in time and restore data, flashback logging is required.

How to read oracle rebuild indexHow to read oracle rebuild indexApr 11, 2025 pm 11:48 PM

Methods to check whether the index has been rebuilt in Oracle: DBA_INDEXES view: view REBUILT value (YES/NO); ALL_INDEXES view: view STATUS value (VALID/UNUSABLE); V$INDEX_STATISTICS view: view NUM_REBUILDS value, indicating the number of index reconstructions.

How to restore oracle upgrade failedHow to restore oracle upgrade failedApr 11, 2025 pm 11:45 PM

After the Oracle upgrade fails, follow the following steps to restore the system: Terminate recovery and switch to recovery mode. Use the recovery command to roll back the data file. Open the database and confirm that the data file is installed and restore the redo log. If the control file is corrupted, recreate it. Reopen the database in full recovery mode. Verify the restore and confirm that the data and objects are intact. If a rollback segment was created during restore, roll it back.

How to modify the oracle database password expiredHow to modify the oracle database password expiredApr 11, 2025 pm 11:42 PM

To modify an expired Oracle database password, follow these steps: 1. Exit all sessions; 2. Connect with the database with SYSDBA permissions; 3. Execute the ALTER USER command to modify the password; 4. Reconnect with the new password; 5. Execute the query to confirm that the password has been modified.

How to deal with oracle escape charactersHow to deal with oracle escape charactersApr 11, 2025 pm 11:39 PM

Escape characters in Oracle are used to indicate special characters or control sequences, including line connections, string delimiters, line breaks, carriage return, tabs, and backspace characters. Escape character processing usually involves escaping special characters in a string, using | concatenating multiline strings, and using a backslash to escape the escape character itself.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.