Select Top 10 rows in MySQL - MS SQL Top keyword equivalent in MySQL

If you are from MS SQL Server background and trying to write query with TOP keyword in MySQL it won't work.

MS SQL Server equivalent of Top keyword in MySQL is Limit


Example 1: Simple Select statement without where clause or order by clause
In MS SQL Server
SELECT Top 10 * 
FROM Customers;

MySQL Equivalent 
SELECT * 
FROM Customers
LIMIT 10;




Example 2: Select statement with where clause
In MS SQL Server
SELECT Top 10 * 
FROM Customers
Where City = 'New York';

MySQL Equivalent 
SELECT * 
FROM Customers
Where City = 'New York'
LIMIT 10;





Example 3: Select statement with where and order by clause
In MS SQL Server
SELECT Top 10 * 
FROM Customers
Where City = 'New York'
Order by CustomerID desc;

MySQL Equivalent 
SELECT * 
FROM Customers
Where City = 'New York'
Order by CustomerID desc
LIMIT 10;





Example 4: Paging Query
In MS SQL Server
SELECT *
FROM
(
    SELECT  
       CustomerID, 
       CustomerName, 
       City,
       ROW_NUMBER() OVER (ORDER BY Customers.CreationDate DESC) as RowNum
    FROM Customers
    Where City = 'New York'
)
WHERE RowNum BETWEEN (@iCurrentPageIndex * @iPageSize) + 1 AND (@iCurrentPageIndex * @iPageSize) + @iPageSize;

MySQL Equivalent 
SELECT * 
FROM Customers
Where City = 'New York'
LIMIT 10,20;