Search code examples
sqlsql-serveransi-sql

ANSI SQL version of SELECT TOP 1

Is there an ANSI SQL compliant version of SQL SERVER's SELECT TOP n?

Solution

  • ANSI/ISO SQL:2003 introduced windowing functions:

    SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY age ASC) AS rownum,
    person_id,
    person_name,
    age
  FROM person
) AS foo
WHERE rownum <= 3

    Microsoft SQL Server 2005 and later supports this syntax. http://msdn.microsoft.com/en-us/library/ms189798(v=sql.90).aspx

    ANSI/ISO SQL:2008 introduced a simpler syntax for FETCH FIRST, which may be more analogous to Microsoft/Sybase TOP syntax:

    SELECT person_id, person_name, age FROM person
FETCH FIRST 3 ROWS ONLY

    Microsoft SQL Server 2012 and later supports this syntax. https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned

    If you're still using Microsoft SQL Server 2000, you should read a question I posted a while back about doing "paging" style queries: Emulate MySQL LIMIT clause in Microsoft SQL Server 2000