You can use a subquery for this like
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.
Update:
To limit the result with both lower and upper bounds things get a bit more bloated with
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
(Copied from specified AskTom-article)
Update 2:
Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
See this answer for more examples. Thanks to Krumia for the hint.
Answer
It's most likely a firewall between SQL Developer and the database that breaks things. You can solve that from SQL Developer using the SQL Developer Keepalive plugin.
You can also fix this from the Database Server by using the answers by Thomas and David Mann.
Oracle Net can be configured with Dead Connection Detection
(SQLNET.EXPIRE_TIME) to workaround this problem. Set EXPIRE_TIME on
the database server to a value less than the firewall connection
timeout so that DCD keeps the connection to the database alive. See
Note 151972.1 "Dead Connection Detection (DCD) Explained"
Original answer
I don't have the answer for this, but I'm experiencing the same problem.
The firewall between my SQL Developer and the database automaticly closes "inactive" sessions. A long running query is according to the firewall an inactive session, so he closes it. I've not, yet, found how to make SQL Developer send packets over a connection with a long running query, so that the firewall doesn't close the connection. And I don't know if this is possible at all.
So I don't think it is a SQL Developer problem, but a firewall issue.
--
UPDATE
There is an extension for SQL Developer that allows you to keep the connections active: http://sites.google.com/site/keepaliveext/
It's not totally finished yet (for example the notification you get states the same timeout no matter what timeout you have specified) but it does the trick. I've not, yet, tested it against the latest SQL Developer pre-release, but it worked with SQL Developer 2.2.x
--
UPDATE
For SQL Developer 4+ you can use: http://scristalli.github.io/SQL-Developer-4-keepalive/
Best Answer
ok guys. figured it out. I was hitting F5 (as in MSSQL query analyzer) to run the query. That is "run script" in this client. F9 or Ctrl + Enter get me to the query results grid view. Hopefully this helps someone else in the future.