Conditional Limit for MYSQL Query

Question

I want to achieve the below 2 scenarios in a single query. (Note- This is just for reference, the actual query is different)

 1. SELECT * FROM CUSTOMER.CUSTOMER LIMIT :startingRow, rowsCount; //WITH LIMIT
 2. SELECT * FROM CUSTOMER.CUSTOMER; // NO LIMIT

Is it possible to write a single conditional query for this? If I pass starting row and rows count params it should go for 1st condition and if no input params are passed, it should give me all records from a table.

What programming language are you using here or else how are you binding the values? — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 22, 2022 at 8:22
This query just for example, I am selecting required columns in my actual query. And I am writing node JS service to read data from MYSQL database data and MYSQL version is 5.7.12-log. So, while executing query from service, there will be 2 scenarios- 1. I will pass limit params to get limited records. 2. I wont pass limit params to get all data — Jayashri Shinde
– Jayashri Shinde, Commented Dec 22, 2022 at 9:04
In this case I would handle this question in js: if the two parameters are supplied, then execute the sql statement that has limit clause. If the two parameters are not supplied, then execute the query without the limit clause. — Shadow
– Shadow, Commented Dec 22, 2022 at 9:16
What if you write both queries and check the condition if the parameters are passed — Johnbosco Adam
– Johnbosco Adam, Commented Dec 22, 2022 at 9:37

Bill Karwin · Accepted Answer · 2022-12-22 15:42:01Z

3

The MySQL manual gives a tip for this:

https://dev.mysql.com/doc/refman/en/select.html

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
SELECT * FROM tbl LIMIT 95,18446744073709551615;

The very large value used in this example is 2⁶⁴-1, or the greatest value of BIGINT UNSIGNED. There are certainly a smaller number of rows in your table.

In your case, you could use 0 as the default offset and a very large value like that as the default limit.

Speaking for myself, I would just run two different queries. One with a LIMIT clause, and the other with no LIMIT clause. Use some kind of if/then/else structure in your client code to determine which query to run, based on whether the function has specified the limit parameters or not.

answered Dec 22, 2022 at 15:42

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Impaler Over a year ago

In an InnoDB (clustered index table) will an unordered SELECT return the rows in a default ordering according to the index tree ordering, or do we need to explicitly add an ORDER BY clause for the offset?

Bill Karwin Over a year ago

@TheImpaler, My understanding is that InnoDB returns rows reliably in the order of the index it reads the rows from. In the example above, I would expect it to read in clustered index order. EXPLAIN should verify the index used by a given query.

Collectives™ on Stack Overflow

Conditional Limit for MYSQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related