Equivalents to SQL Server TOP

Question

In SQL Server, TOP may be used to return the first n number of rows in a query. For example,

SELECT TOP 100 * FROM users ORDER BY id

might be used to return the first 100 people that registered for a site. (This is not necessarily the best way, I am just using it as an example).

My question is - What is the equivalent to TOP in other databases, such as Oracle, MySQL, PostgreSQL, etc? If there is not an equivalent keyword, what workarounds can you recommend to achieve the same result?

Answer 1

This is standard SQL (Oracle and SQL Server implement it). This is an example of returning up to 100 rows:

        SELECT ID_CONTROL FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID_CONTROL) 
        ROWNUMBER, ID_CONTROL FROM IWS_CONTROL WHERE 
        CURRENT_STATE = 15 AND CURRENT_STATUS=0) A WHERE ROWNUMBER <= 100)

Answer 2

In DB2 you would make your query look like this:

SELECT * FROM tblData FETCH FIRST 10 ROWS ONLY;

Answer 3

In Oracle you want to use a TOP-N query.

For example:

select  *
  from  (SELECT  *
           FROM  foo
          where  foo_id=[number]
       order by  foo_id desc)
 where  rownum <= 3

This will get you the top three results (because I order by desc in the sub query)