Equivalent of SQL "TOP X" in Oracle
A friend asked me a good question, so thought I'd share it and the answer for whomever it may help in the future.
Q: What is the equivalent of the SQL statement "SELECT TOP 10 * FROM myTable" in Oracle?
A: SELECT * FROM myTable WHERE ROWNUM < 11
Q: What is the equivalent of the SQL statement "SELECT TOP 10 * FROM myTable" in Oracle?
A: SELECT * FROM myTable WHERE ROWNUM < 11
Subscription Options
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
Re: Equivalent of SQL "TOP X" in Oracle
thank you
Posted by Azzedo on July 1, 2009 at 4:39 PM
Re: Equivalent of SQL "TOP X" in Oracle
Hi mates,
Well, Firstly I have to thank you for you tip, it's pretty useful when we facing such situation.
Just to complete this article, the TOP function from SQL processes first the order by clause and after the TOP function... on oracle databases we have to do a "workaround" to get the expected result as shown below:
SELECT
BYMD
FROM
(SELECT BYMD FROM BRZ_AC_TMP_INFO ORDER BY BYMD DESC)
WHERE ROWNUM < 4
it's going to process the order by clause followed by the WHERE clause.
I know it's not even close the best solution but can save a good time.
Regards
Paulo Miguel Almeida
Twitter: @paulomigueljava
Facebook: http://www.facebook.com/paulo.miguel.almeida
Well, Firstly I have to thank you for you tip, it's pretty useful when we facing such situation.
Just to complete this article, the TOP function from SQL processes first the order by clause and after the TOP function... on oracle databases we have to do a "workaround" to get the expected result as shown below:
SELECT
BYMD
FROM
(SELECT BYMD FROM BRZ_AC_TMP_INFO ORDER BY BYMD DESC)
WHERE ROWNUM < 4
it's going to process the order by clause followed by the WHERE clause.
I know it's not even close the best solution but can save a good time.
Regards
Paulo Miguel Almeida
Twitter: @paulomigueljava
Facebook: http://www.facebook.com/paulo.miguel.almeida
Posted by Paulo Miguel Almeida on July 13, 2011 at 1:35 PM