LIMIT vs. ROWNUM in MariaDB: Choosing the Right Approach for Row Selection

• Purpose
  Its primary purpose is to provide compatibility with Oracle code that uses ROWNUM. For native MariaDB applications, the LIMIT clause is generally preferred due to its simplicity and predictability.
• Functionality
  It assigns a sequential number starting from 1 to each row in the result set. This numbering respects the ORDER BY clause, meaning rows are numbered based on the sorting order.

Using ROWNUM

• LIMIT
  
  • Offers a more straightforward approach for limiting rows.
  • Syntax: SELECT * FROM your_table ORDER BY your_column LIMIT 10; (This selects the first 10 rows ordered by your_column)
• ROWNUM
  
  • Requires a subquery to achieve predictable results for limiting rows. (e.g., selecting rows where ROWNUM is less than or equal to a specific number within a subquery)
  • Syntax: SELECT * FROM (SELECT * FROM your_table ORDER BY your_column) AS subquery WHERE ROWNUM() <= 10; (This selects all rows from subquery where ROWNUM is less than or equal to 10)

When to consider ROWNUM

While LIMIT is generally preferred, you might encounter situations where:

• You need more complex row numbering logic within the query (although window functions introduced in MariaDB 10.2+ offer more powerful options for this).
• You're working with existing Oracle code that relies on ROWNUM.

• MariaDB versions below 10.2 don't support window functions like ROW_NUMBER(), which offer more advanced row numbering capabilities compared to ROWNUM.

---

Example 1: Using ROWNUM (for compatibility with Oracle code)

-- Assuming a table 'customers' with columns 'id' and 'name'

-- Simulating Oracle's ROWNUM to fetch top 5 customers
SELECT * FROM (
  SELECT id, name, ROWNUM() AS row_num
  FROM customers
  ORDER BY name ASC
) AS subquery
WHERE row_num <= 5;

This code achieves the same result as the next example using LIMIT. However, it requires a subquery to achieve predictable row limiting.

Example 2: Using LIMIT (preferred method for MariaDB)

-- Fetching top 5 customers with a simpler approach
SELECT id, name
FROM customers
ORDER BY name ASC
LIMIT 5;

This code directly uses LIMIT after the ORDER BY clause to fetch the first 5 rows sorted by name (ascending). This is generally the recommended approach for MariaDB as it's more concise and efficient.

Example 3: Using ROWNUM for complex logic (limited use case)

Note
This is an example for illustrative purposes only. Window functions (available in MariaDB 10.2+) offer better solutions for complex row numbering.

-- Assuming a table 'orders' with columns 'id', 'customer_id', 'order_date'

-- Selecting orders placed within the last week and assigning a row number 
-- within that timeframe (limited functionality compared to window functions)
SELECT id, customer_id, order_date, ROWNUM() AS row_num_within_week
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY
ORDER BY order_date DESC
HAVING row_num_within_week <= 3;

---

1. LIMIT
   This is the preferred method for MariaDB due to its simplicity and predictability. It allows you to directly specify the number of rows you want to retrieve after the ORDER BY clause.

2. Window Functions (MariaDB 10.2+)
   These functions offer more advanced capabilities for assigning row numbers and performing calculations based on a defined window of rows. Here are some relevant window functions:

   • ROW_NUMBER()
     This function assigns a unique, sequential number to each row within a specified ordering. It's more flexible than ROWNUM as you can define the window frame (e.g., only considering rows within a specific partition).

   Example:

   SELECT id, customer_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_num_per_customer
   FROM orders;
   

   This assigns a unique row number for each order within each customer, sorted by order date (descending).

   • RANK() and DENSE_RANK()
     These functions assign ranks to rows based on the sorting order. RANK() allows gaps in the ranking (e.g., two rows with the same value might get the same rank), while DENSE_RANK() avoids gaps.

Choosing the Right Alternative

• For compatibility with existing Oracle code that relies on ROWNUM
  You can use ROWNUM, but it's generally less efficient and flexible than the other options.
• For assigning unique row numbers with more control over the window frame or needing ranking functionality
  Use window functions like ROW_NUMBER(), RANK(), or DENSE_RANK().
• For simple row limiting based on order
  Use LIMIT.

Additional Points

• If you're using MariaDB versions below 10.2, you won't have access to window functions, so LIMIT would be your primary alternative to ROWNUM.
• Window functions offer various other functionalities beyond row numbering, making them a powerful tool for complex data manipulation within queries.