Select from MySQL from a given string to the next given string

Question

Image with part of the database in question

I have this table

id ¦ Follow 
1 ¦ Route 1
2 ¦ Store 1
3 ¦ Store 2
4 ¦ end
5 ¦ Route 2
6 ¦ Store 3
7 ¦ Store 4
8 ¦ end
etc, etc

So, what I try to understand is how can I get only the rows starting from Route Number (i.e Route 2) until the first "end" is found? Routes are subject to change so it have to be from string "Route number" to the next "end" met.

The solution may be very simple but I have no clue where to start from, as I am quite new in this area and I haven't found anything relevant on SO. Please let me know if you want me to be more specific.

Thanks!

Using Like queries is a good Option. Here is an example as a start stackoverflow.com/questions/2100360/… Found another one stackoverflow.com/questions/13019482/mysql-select-until-in-sql — Rahal Danthanarayana
– Rahal Danthanarayana, Commented May 13, 2017 at 19:30
You cann't do this with simple query. Try to use stored procedure or just do this from your client code. — Evgeny
– Evgeny, Commented May 13, 2017 at 19:31
SQL tables represent unordered sets. There is no concept of next unless you have a column that specifies the ordering. — Gordon Linoff
– Gordon Linoff, Commented May 13, 2017 at 19:35
I've added a screen shot with the database in phpmyadmin. Maybe will bring more light. — ceuca ciprian
– ceuca ciprian, Commented May 13, 2017 at 19:40

Gordon Linoff · Accepted Answer · 2017-05-13 19:37:19Z

1

Let me assume that you have a column that specifies the ordering, uniquely. I'll call it id. Then you can do:

select t.*
from t cross join
     (select id
      from t
      where name = 'Route 2'
     ) tt
where t.id >= tt.id and
      t.id <= (select min(t2.id)
               from t t2
               where t2.id > t.id and
                     t2.name = 'end'
              );

I should note that you have a very arcane data structure. I think you might reconsider how the data is stored.

answered May 13, 2017 at 19:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Dhruv Saxena Over a year ago

@ceucaciprian This actually works if where t2.id > t.id is changed to where t2.id > tt.id - sqlfiddle.com/#!9/1c6f1/4

ceuca ciprian Over a year ago

Wow! Thank you so much, Druv Saxena! And thank you again, Gordon!

Collectives™ on Stack Overflow

Select from MySQL from a given string to the next given string

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related