Selecting only the first row - compatiable with Mysql/SQL Server?

Question

Is there a way to create a query to select only the first result that's compatible for both MySQL and SQL Server?

SELECT TOP 1 mycolumn, mycolumn2
FROM mytable
ORDER BY ID DESC

works for SQL Server.

but

SELECT mycolumn, mycolumn2
FROM mytable
ORDER BY ID DESC
LIMIT 1

works only for MySQL.

I'm not looking to download any PDO or database abstraction class - I'm wondering if there are SQL queries compatibility to both?

Answer 1

Is there a way to create a query to select only the first result that's compatible for both MySQL and SQL Server?

Short answer, no.

Long answer, not in any way efficiently. Somebody else suggested WHERE id = MAX(id). That will work if id is the primary key. But not with the kind of optimization that the TOP and/or LIMIT features provide.

Sorry.

Answer 2

If you want to get TOP 1, you can use max() -- DESC or min() -- ASC. But if you want get TOP N, you can try something like this:

SELECT mycolumn, mycolumn2
FROM mytable M1
WHERE (SELECT COUNT(*)
       FROM mytable M2
       WHERE M1.ID < M2.ID) < N  -- M1.ID < M2.ID: DESC, < N: TOP N