Oracle: ROWNUM doesn’t equals

I often forget ROWNUM and the equals quirk, also not forgetting the ORDER BY/WHERE and ROWNUM combination issue too.
-- Works - result is two rows (bye, hello)
SELECT * FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
) WHERE ROWNUM < 3;

-- Doesn't
SELECT * FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
) WHERE ROWNUM = 2;

This is apparently because (I quote) “the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row” from Oracle ROWNUM Documentation.
So it is calculated on the select, this means you have to be careful with ORDER BYs and WHEREs (etc) and explains why it doesn’t work as we hoped.

-- So this works; returns one row (hello)
SELECT "Greeting" FROM
(
SELECT "Greeting", ROWNUM AS "N" FROM (
SELECT 'hello' AS "Greeting" FROM DUAL
UNION SELECT 'bye' AS "Greeting" FROM DUAL
UNION SELECT 'hi' AS "Greeting" FROM DUAL
)
) WHERE N = 2;

There are other ways, but I am only focusing on ROWNUM in this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s