MySQL / MySQLi selects from table until setpoint is found

Question

I have been looking for quite a while and cannot find something that works for me, since each similar question is about reaching a certain column amount.

I am looking for a way to search for records in a table, all of which have a number of common column values ​​- they are not sequential records in a table, but they should be sequential records when common values ​​are taken into account - and all records are retrieved until one particular column has another values.

Given the data:

PrimaryID SecondaryID TertiaryID Limit DateTime 12 9 4 1 2013-03-22 00:00:14 11 9 4 1 2013-03-22 00:00:13 10 54 11 0 2013-03-22 00:00:12 9 9 4 1 2013-03-22 00:00:11 8 9 23 1 2013-03-22 00:00:10 7 9 4 1 2013-03-22 00:00:09 6 9 4 0 2013-03-22 00:00:08 5 9 16 0 2013-03-22 00:00:07 4 72 32 0 2013-03-22 00:00:06 3 9 4 1 2013-03-22 00:00:05 2 9 4 1 2013-03-22 00:00:04 1 9 4 0 2013-03-22 00:00:03 

I am trying to get records with both SecondaryID = 9 and TertiaryID = 4 to Limit = 0 (sorted by DateTime DESC).

Using PrimaryID 12 as a starting point, I would expect the following:

 PrimaryID SecondaryID TertiaryID Limit DateTime 12 9 4 1 2013-03-22 00:00:14 11 9 4 1 2013-03-22 00:00:13 9 9 4 1 2013-03-22 00:00:11 7 9 4 1 2013-03-22 00:00:09 

And for PrimaryID 3 as a starting point:

 PrimaryID SecondaryID TertiaryID Limit DateTime 3 9 4 1 2013-03-22 00:00:05 2 9 4 1 2013-03-22 00:00:04 

I am currently retrieving each result for SecondaryID = 9 and TertiaryID = 4, and then iterating over it in PHP - and currently hundreds of results (and growing every week) work too slowly in production.

I would also happily agree with a specific MySQLi solution, since we are going to upgrade to MySQLi instead of MySQL.

Answer 1

I realized that I was looking for this request incorrectly.

Instead of “counting back” from a given point in the data, to see when Limit first became = 1 in the sequence, I instead had to look for the previous occurrence of Limit = 0, and then query forward to my original PrimaryID.

As a result of a query that covers everything I need,

 SELECT * FROM table1 WHERE SecondaryID='9' AND TertiaryID='4' AND `Limit`='1' AND PrimaryID<='3' AND `DateTime`>= (SELECT `DateTime` FROM table1 WHERE SecondaryID='9' AND TertiaryID='4' AND `Limit`='0' AND PrimaryID<='3' ORDER BY `DateTime` DESC LIMIT 1) ORDER BY `DateTime` DESC 

Answer 2

Request will be

 SELECT * FROM table_name WHERE PrimaryID <= 12(your_id) AND SecondaryID=9 and TertiaryID=4 AND Limit=1 ORDER BY DateTime DESC 

It is tested and working.