In SQL Server, TOP might be accustomed to return the very first n quantity of rows inside a query. For instance,

Choose Best Players * FROM customers ORDER BY id
might be employed to return the very first 100 people who registered for any site. (This isn't always the easiest way, I'm simply using it for example).

My real question is - What's the equal to TOP in other databases, for example Oracle, MySQL, PostgreSQL, etc? If there's no equivalent keyword, what workarounds are you able to recommend to offer the same result?

To choose first 100 rows:

MySQL and PostgreSQL:

SELECT  *
FROM    Table
ORDER BY
        column
LIMIT 100

Oracle:

SELECT  *
FROM    (
        SELECT  t.*
        FROM    table
        ORDER BY
                column
        )
WHERE   rownum <= 100

Note that you'll require a subquery here. If you do not give a subquery, ROWNUM will choose first 10 rows in random order after which sort them by column.

To choose rows between 100 and 300:

MySQL:

SELECT  *
FROM    TABLE
ORDER BY
        column
LIMIT   100, 200

PostgreSQL:

SELECT  *
FROM    Table
ORDER BY
        column
OFFSET 100 LIMIT 200

Oracle:

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (ORER BY column) AS rn
        FROM    table
        )
WHERE   rn >= 100
        AND rownum <= 200

Observe that an effort to simplify it with ROWNUM BETWEEN 100 AND 200 (instead of rn BETWEEN 100 AND 200 within the outer query) will return nothing in Oracle!

RN BETWEEN 100 AND 200 works in Oracle too but is less capable.

Begin to see the article during my blog for performance particulars:

For Postgres and MySQL it is the LIMIT keyword.

SELECT *
  FROM users
 ORDER BY id
 LIMIT 100;

This really is standard SQL (Oracle and SQL Server implement it). It is really an illustration of coming back as much as 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)
LIMIT 100

as with

SELECT * FROM foo ORDER BY bar LIMIT 100

You should use RANK() and DENSE_RANK() in Oracle. Here is really a connect to AskTom website explaining how you can to pagination and top-n queries with DENSE_RANK in Oracle.

Oracle:

select * from (select * from foo ORDER BY bar) where rownum < 100

Having a nice explanation regarding how to have great results in AskTom.

In Ingres exactly the same query would by:

select First 100 * from foo ORDER BY bar

Ingres question had been clarified in StackOverflow before.

In DB2 you'd build your query seem like this:

Choose * FROM tblData FETCH FIRST 10 ROWS ONLY

In SQL Anywhere, it's just like SQL Server:

SELECT TOP 100 * FROM users ORDER BY id

You may also start in the center of the end result set if you would like:

SELECT TOP 100 START AT 50 * FROM users ORDER BY id

will get the 50th through 150th rows from the result set.

In Oracle you need to make use of a TOP-N query.

For instance:

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

This can enable you to get the very best three results (because I order by desc within the sub query)