SELECT max(a) FROM t WHERE a < (SELECT max(a) FROM t WHERE a < (SELECT max(a) FROM t));
What will the above return if the table is less than 3 in cardinality?

In most databases, the order of rows in the table are not required to be consistent from one time to another unless you use the ORDER BY statement. Even though it appears that you always see the same order when you SELECT * FROM tbl, you cannot rely on that as new versions of the database come out.

So the answer to that question depends on what you order by. If you have a customer table ordered by last name, what does the "third row" tell you? Please explain a scenario where getting the third row would be required.

You can use this and can select whichever row you want to select:

select * from (select rownum r,col_1,col_2,col_3,col_4 from table_name) t
where t.r='&z'