Sunday, November 06, 2011

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;

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape