tsJensen

A quest for software excellence...

SQL Server TOP vs MySQL LIMIT

I've recently begun to explore MySQL a bit more, mostly out of curiosity. Primarily I work with SQL Server 2005 in my day job but it seems prudent to know a bit more about some of the other databases servers and it's been a while since I dusted off MySQL.

Happily I found that MySQL AB has released a really great ADO.NET provider for MySQL.

If you have used the SQL Server specific ADO.NET provider, you'll easily make the transition to using this one.

But I digress. The reason for this posting was to permanently remind myself and anyone else making a transition between these two database engines that TOP = LIMIT but not in the same place. That is to say, the TOP keyword is used in SQL Server to limit the number of returned rows just prior to the column specifiers. The LIMIT keyword however is used at the end of the select statement. Thus:

SELECT TOP 1 * FROM MYTABLE WHERE FKID = 3

And for MySQL use:

SELECT * FROM MYTABLE WHERE FKID = 3 LIMIT 1;

Yes, I know. It ought to be obvious, but having a quick reminder here will help to seal this minor difference into my brain.