Daemonite: TOP and LIMIT in different databases Archive

Daemonite: TOP and LIMIT in different databases Archive


Saturday, March 12, 2005
TOP and LIMIT in different databases

Rather than specialising in one particular database platform we seem to be moving to support more and more in recent years. With the release of FarCry CMS to open source we now support MS SQL, Oracle, Postgresql and mySQL database servers. Funny that for a language that is seemingly standardised how many little idiosyncracies SQL has across different databases.

Postgresql:
SELECT firstname, lastname, email
FROM contacts
LIMIT 10

MS SQL Server:
SELECT TOP 10 firstname, lastname, email
FROM contacts

ORACLE:
SELECT firstname, lastname, email
FROM contacts
WHERE ROWNUM <= 10

mySQL:
SELECT firstname, lastname, email
FROM contacts
LIMIT 10

Posted by modius at 12:36 PM | Permalink
Trackback: http://blog.daemon.com.au/cgi-bin/dmblog/mt-tb.cgi/271

Comments

It's worth mentioning that with MySQL (and Postgres, I believe) you can also supply an offset: LIMIT 20, 10

That'll return the ten rows that follow the first twenty (or page three of a ten-per-page paged display). If omitted, it defaults to zero, indicating to not skip any rows.

Oracle provides even more flexiblity with it's ROWNUM psuedo-column and the ability to use any kind of conditional on it.

Posted by: Barney on March 12, 2005 01:09 PM

beware of ordering and rownum in oracle though, eh? Need an inner view approach.

Interesting tidbit we just discoverd. We are using CFMX and Oracle 9i and the JDBC drivers shipping with CFMX. the maxrows attribute of cfquery actually limits the result set at the DB.

Posted by: Douglas Knudsen on March 12, 2005 03:25 PM

When moving from the MySQL arena to MS SQL, I sometimes "miss" the ability to pass two parameters in LIMIT to section off a portion of the data.

I achieve the effect with a combination of TOP and the CFQUERY maxrows, but this is not really a purely SQL approach, of course. What is the best MS SQL way to achieve this?

Posted by: James Edmunds on March 13, 2005 02:13 AM