query With ROWNUM Showing wrong results (Doc ID 2745471.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

User getting wrong result with "rownum" used. And getting correct result when "fetch first row" is used.

Example:

SELECT .. WHERE .. ORDER BY ..;

FO F
-- -
aa W >>>> output expected when using rownum
aa X
bb X
aa W
aa X
aa W
aa W
aa W
cc E
bb X

SELECT * FROM (SELECT ... ORDER BY ..) WHERE ROWNUM < 2;

FO F
-- -
bb X

SELECT .. FROM <table_name> WHERE .. ORDER BY ..) fetch first row only;

FO F
-- -
aa W

select from (... order by ...) where rownum = 1

returns not the "expected" row.

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

Symptoms

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.