Development

Random items dealing with Development

SQL Reference (LIMIT / TOP / ROWNUM / FETCH FIRST)

Here is a quick reference for Limits in other DBMSes.

Source: wikipedia

SELECT TOP 10 Field_1
FROM Tbl;
MS SQL Server, Sybase ASE, MS Access

SELECT
   Field_1
FROM Tbl 
LIMIT 10 OFFSET 20;
Netezza, MySQL, PostgreSQL (also supports the standard, since version 8.4), SQLite, HSQLDB, H2

SELECT
   Field_1
FROM Tbl 
WHERE ROWNUM <= 10;
Oracle (also supports the standard, since Oracle8i)

SELECT FIRST 10 Field_1
FROM Tbl;
Ingres

SELECT FIRST 10 
   Field_1
FROM Tbl
ORDER BY Field_1;
Informix

SELECT SKIP 20 FIRST 10 
   Field_1,
   Field_2,
   Field_3
FROM Tbl
ORDER BY Field_1, Field_2;
Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)

SELECT TOP 10 START AT 20 *
FROM Tbl;
Sybase SQL Anywhere (also supports the standard, since version 9.0.1)

SELECT FIRST 10 SKIP 20 *
FROM Tbl;
Interbase, Firebird

SELECT
   Field_1
FROM Tbl 
ROWS 20 TO 30;
Firebird (since version 2.1)

SELECT *
FROM Tbl
WHERE ID_Field > 10
FETCH FIRST 10 ROWS ONLY;
DB2

SELECT
   ID_Field
FROM Tbl
WHERE ID_Field > 20
FETCH FIRST 10 ROWS ONLY;
DB2 (new rows are filtered after comparing with key column (ID_Field) of table Tbl)

source:wikipedia

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: