I am currently working on all 3 major databases now: SQL Server and Oracle for my 9 to 5 job; MySQL for personal use. One day one of my friends asked me, “How do you limit row count to only show the first 10 rows in Oracle?” And I quickly answered him “Oh… add limit 10 at the end”. Right after I said that, I realized that it does not work for Oracle because it is a MySQL syntax!
Here is a table for quick comparison for the syntax difference for SQL Server, Oracle and MySQL. All of these examples do the exact same thing: return 10 rows from a result set.
| Database | Last 10 login User (sort by login date and limit to 10 row return) |
| SQL Server | select TOP 10 * from users order by lastlogin desc |
| Oracle | select * from users WHERE ROWNUM<=10 order by lastlogin desc |
| MySQL | select * from users order by lastlogin desc LIMIT 10 |
I personally like MySQL syntax the best! It is easy to understand and easy to remember and ofcourse makes total sense!
