Ansi SQL Version of Select Top 1

ANSI SQL version of SELECT TOP 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 has supported this syntax since SQL Server 2005.
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 does not yet support this syntax. See also http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows

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

Alternative SQL ANSI for TOP WITH TIES

Here is a third option for SQL Server:

WITH cte AS (
    SELECT p.id_category, COUNT(*) AS cnt
    FROM product p 
    INNER JOIN category c
        ON p.id_category = c.id_category
    GROUP BY p.id_category
)

SELECT *
FROM cte
WHERE cnt = (SELECT MAX(cnt) FROM cte);

If you also cannot rely on CTEs being available, you can easily enough just inline the CTE into the query. From a performance point of view, DENSE_RANK would probably outperform my answer.

With the CTE removed this becomes:

SELECT *
FROM
(
    SELECT p.id_category, COUNT(*) AS cnt
    FROM product p 
    INNER JOIN category c
        ON p.id_category = c.id_category
    GROUP BY p.id_category
)
WHERE cnt = (SELECT MAX(cnt) FROM (
                 SELECT p.id_category, COUNT(*) AS cnt
                 FROM product p 
                 INNER JOIN category c
                     ON p.id_category = c.id_category
                 GROUP BY p.id_category
            ));

This query would even run on MySQL. As you can see, the query is ugly, which is one reason why things like CTE and analytic functions were introduced into the ANSI standard.

Database agnostic query to select top 1 or top n rows?

Per the thread you linked to, there isn't much of a database agnostic solution:

• DB2 -- select * from table order by col fetch first 10 rows only
• Informix -- select first 10 * from table order by col
• Microsoft SQL Server and Access -- select top 10 * from table order by col
• MySQL and PostgreSQL -- select * from table order by col limit 10
• Oracle 8i -- select * from (select * from table order by col) where rownum <= 10

http://forums.mysql.com/read.php?60,4515,4678

How to select top N from a table

For SQL Server:

select top 25 * from table order by Num asc

For mySQL:

select * from table order by Num asc limit 25

Equivalent of SQL Server's TOP 1 in Oracle (without using rownum or row_number())

Oracle does not support TOP 1, as you pointed out. You might be able to rewrite in Oracle while maintaining the correlated subquery, but the best option would probably be to remove that subquery, and instead just use the join you already making to handle the logic:

WITH cte AS (
    SELECT
        PA.*,
        COALESCE(CI.DISCOUNTCODE, 'NA') AS DISCOUNTCODE,
        ROW_NUMBER() OVER (PARTITION BY CI.ID ORDER BY CI.DATE DESC) rn
    FROM PAYMENT PA
    LEFT JOIN CONTRACTINFO CI
        ON PA.CONTRACTID = CI.ID AND
           CI.DATE < PA.PAYMENTDATE 
    WHERE
        CI.BASERECORD = 1
)

SELECT CONTRACTID, TIME, PAYMENTDATE, DISCOUNTCODE
FROM cte
WHERE rn = 1;

There are a method to paging using ANSI SQL only?

See Limit—with offset section on this page: http://troels.arvin.dk/db/rdbms/

BTW, Firebird also supports ROWS clause since version 2.0

SQL - Select first 10 rows only?

In SQL server, use:

select top 10 ...

e.g.

select top 100 * from myTable
select top 100 colA, colB from myTable

In MySQL, use:

select ... order by num desc limit 10

LIMIT N vs TOP N in SQL Server

DMBS vendors have proprietary syntax to provide LIMIT functionality. A draft of the ANSI/ISO standards allows a OFFSET and FETCH clauses, which is also implemented in SQL Server. ORDER BY is required (and with TOP also) to provide predictable results.

Below is an alternative:

SELECT * FROM SalesRawData
WHERE Title = N'Sriracha' 
ORDER BY Title OFFSET 0 ROWS FETCH FIRST 100 ROWS ONLY;

Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?

this shows the different ways:

-- DB2
select * from table fetch first 10 rows only 
-- Informix 
select first 10 * from table 
-- Microsoft SQL Server and Access 
select top 10 * from table 
-- MySQL and PostgreSQL 
select * from table limit 10 
-- Oracle 
select * from (select * from table) where rownum <= 10

---

Related Topics