ANSI SQL version of SELECT TOP 1

Question

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

Ced

Is there an option to ensure that sql-server gives warnings for Non-ANSI-SQL commands? A sql compatibility level to set ANSI compliance would be great. But to my understanding there is no option like this. – Hogfish

Answer 1

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

Taper

I did not even know that ROW_NUMBER() OVER was ANSI SQL? – Ced

Answer 2

Some additional context, SQL Server has the following TOP syntax:

[   
    TOP (expression) [PERCENT]  
    [ WITH TIES ]  
]

The relevant standard SQL equivalents are:

FETCH FIRST expression ROWS ONLY
FETCH FIRST expression PERCENT ROWS ONLY
FETCH FIRST expression ROWS WITH TIES

Many SQL implementations only implement ROWS ONLY, in case of which the standard SQL equivalent would be to filter on:

-- PERCENT ROWS ONLY
percent_rank () OVER (ORDER BY ..) <= expression / 100

-- ROWS WITH TIES
rank () OVER (ORDER BY ..) <= expression

Subadar

Answer 3

'LIMIT' works with Amazon's Athena:

SELECT * FROM myTable LIMIT 3;

3rd party edit

Quote from athena

Amazon Athena is a serverless, interactive analytics service built on open-source frameworks, supporting open-table and file formats. Athena provides a simplified, flexible way to analyze petabytes of data where it lives. Analyze data or build applications from an Amazon Simple Storage Service (S3) data lake and 25-plus data sources, including on-premises data sources or other cloud systems using SQL or Python. Athena is built on open-source Trino and Presto engines and Apache Spark frameworks, with no provisioning or configuration effort required.

Latanya

Does athena strive to be "query compatible" to sql-server? – Hogfish