Using LIMIT in MySQL and FETCH FIRST in SQL Server

Query with LIMIT in MySQL and FETCH FIRST in SQL Server

Write a SQL query to retrieve the top 5 highest-paid employees, using LIMIT for MySQL and FETCH FIRST for SQL Server.

Solution:

-- MySQL
SELECT EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

-- SQL Server
SELECT EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to limit query results using platform-specific syntax.
• Key Components :
• LIMIT (MySQL): Restricts the number of rows returned.
• FETCH FIRST (SQL Server): Achieves the same result with different syntax.
• ORDER BY Salary DESC: Ensures the highest salaries are prioritized.
• Why Use Platform-Specific Syntax?:
• Each database system has unique features for limiting results.
• Understanding these differences ensures efficient query writing.
• Real-World Application :
• In reporting systems, limiting results improves performance and usability.

Additional Notes:

• Use LIMIT in MySQL/PostgreSQL and FETCH FIRST in SQL Server/Oracle.
• Avoid mixing syntax unless using a database abstraction layer.
• Important Considerations:
• Standardize query logic for portability where possible.

For more Practice: Solve these Related Problems:

• Write a SQL query to retrieve the top 10 most expensive products, using LIMIT for MySQL and FETCH FIRST for SQL Server.
• Write a SQL query to retrieve the top 3 highest-scoring students, using LIMIT for MySQL and FETCH FIRST for SQL Server.
• Write a SQL query to retrieve the top 7 latest orders, using LIMIT for MySQL and FETCH FIRST for SQL Server.
• Write a SQL query to retrieve the top 5 oldest employees, using LIMIT for MySQL and FETCH FIRST for SQL Server.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Comparing how Window Functions are Implemented in SQL Server and PostgreSQL.
Next SQL Exercise: Handling String Concatenation Across Databases.