Can MySQL/MariaDB stop doing a selection by reaching certain condition? Or select all rows before co-CodePudding

For example, I have table

id;name
1;John
2;Mary
3;Cat
4;Cheng

I want selection to stop right after 3;Cat and still have as much rows in it as exist berore 3;Cat I think this could be described with such a query

SELECT * FROM table WHERE condition ORDER BY id LIMIT name = 'Cat'

but of course there is no such a construction LIMIT name='Cat' in SQL. Maybe something else fits?

Currently Im using extensive select, but it requires enormous 1200 rows to be sure that it has at least one record expected.

CodePudding user response：

This is a not-so-ad answer https://stackoverflow.com/a/22232897/1475428

Solution might look like

SELECT * WHERE id <= (SELECT MIN(id) WHERE name = 'Cat') order by id

MIN function plays role of backward approach that works like conditional LIMIT.

This looks like an ugly way, I still think there might be a better solution.

CodePudding user response：

This is quite awkward to do in a single query. That means you probably should not try to do it in a single query.

Sometimes it's simpler to do a complex task in several steps. It's easier to write, it's easier to debug, it's easier to modify if you need to, and it's easier for future programmers to read your code if they need to take over responsibility.

So first query for the condition, and find out the id of the row you want to stop at:

SELECT MIN(id) FROM mytable WHERE name = 'Cat';

This returns either an id value, or else NULL if there is no row matching the condition.

If that result was not NULL, then use that value to run a simple query:

SELECT * FROM mytable WHERE id <= ? ORDER BY id

Else if the result was NULL, then default to a query with the fixed LIMIT you want:

SELECT * FROM mytable ORDER BY id LIMIT ?

If you have special conditions that aren't supported by simple SQL, then break it up into different queries that are each simple, and use a little bit of application logic to choose which query to run.