Sort rows away from each other.

[flexgrip]

I know how to do basic sorting according to id number or alphabetical sorting based on, say, the last name of a person. I also figured out how to sort using criteria from multiple tables, but I am curious the method someone would use to sort rows away from each other. Let me explain.

 

Let's say I have a database for a grade school musical act. I have 20 students. So I put them in the database for each act they are in. So:

 

id - name - act_number

1 - bobby - 1

2 - sue - 1

3 - bobby - 2

4 - dylan - 2

5 - jimmy - 3

 

So now I want to write a script that will determine what order the acts run in. Obviously bobby, being in two acts, needs time to change so we cant have act_number 1 and 2 be back to back. In reality there would be a lot more people in multiple acts, so we would want to determine the best schedule giving these people the most time to change and get ready if they are in multiple acts.

 

How would I sort this? I am assuming I will have to add more fields for each row to help determine the best possible schedule... maybe even another table? Have any of you ever come across a script like this or is there something build into mysql 5 that might help me?

 

I really appreciate any help to get me pointed in the right direction. This puzzle has turned out kind of fun but I just can't figure it out.

[flexgrip]

Sorry, I forgot to add that I think I will need to use GROUP BY but I just can't wrap my head around how that would work in this scenario.

[Ken2k7]

SELECT * FROM table_name
ORDER BY act_number, name
GROUP BY act_number

 

What about that?

[flexgrip]

I cant really test that. I made a fake table with the same data from above (with like 20 rows total. several different people in different acts)

 

It is telling me error in my syntax near GROUP BY act_number. I read somewhere that you must select the column that you are grouping by. But I tried that as well and it didnt work. Any ideas?

[Ken2k7]

What do you want the outcome to be?

 

Sorry, no GROUP BY. Is this what you want? You didn't specify what results you want returned.

SELECT * FROM table_name
ORDER BY act_number, name

[flexgrip]

I am looking for an ordered list of act numbers. So from the example rows above, I want to spit out a list of the act_numbers that will put the most space between the acts with the same person. So from above, I would want act number 3 to be placed between act number 1 and 2 because bobby is in both 1 and 2.

 

If I had 100 people and some people were in multiple acts I would want it to figure out the order of all the acts so that people werent back to back. Let me know if I am making any sense because this is hard to explain.

[flexgrip]

Let me just give you an example table and what I would want the output to be.

 

So here is my table of students and the acts they are in.

id - name - act_number

1 - bobby - 1

2 - sue - 1

3 - bobby - 2

4 - kim - 3

5 - larry - 4

6 - mark - 5

7 - tommy - 6

8 - edward - 7

9 - nathan - 8

10 - scotty - 9

11 - henry - 9

12 - scotty - 10

13 - rick - 10

 

If I were to sort the act numbers ascending then they would go 1-10. But that would place bobby in back to back acts, and scotty in back to back acts. I would like it to order the acts so that they wouldn't be close to each other. So I would want the act order to be something like

 

1

9

3

4

5

2

10

6

7

8

 

This way the nights schedule would give them time to change. Now say by rearranging the acts like I did above it put someone else back to back, I want it to be smart enough to figure that out as well and no matter what the circumstance, not put anyone that is in multiple acts back to back.

 

So if the table were in this order(putting sue in act 1 and 9:

id - name - act_number

1 - bobby - 1

2 - sue - 1

3 - bobby - 2

4 - kim - 3

5 - larry - 4

6 - mark - 5

7 - tommy - 6

8 - edward - 7

9 - nathan - 8

10 - scotty - 9

11 - sue - 9

12 - scotty - 10

13 - rick - 10

 

Arranging the table by hand like I originally did would place HER in back to back acts.

 

Maybe that will make a little bit more sense.

[fenway]

The problem is that mysql is set-based -- so it doesn't know about the "next" or "previous" row -- your'e asking it to be psychic.

[flexgrip]

So to accomplish this would it need to be a php script that figures all of this out? I understand what you are saying though. MySQL doesn't know/care what order the rows are in. Would I just need to find a really handy php script to do this? I am sure someone else before me has wanted to do this. Any pointers to get me going in the right direction? I don't care to write the script, even if it takes weeks. I just need a kick in the butt.

[Ken2k7]

Still, even with PHP, this is tricky. How would someone know to put 9 after 1? They're just arbitrary numbers.

[flexgrip]

If it was just 10 people with only a small number in multiple acts I could just do select all people in multiple acts and if the different of the act numbers was 1 I could then relocate the act. But I am expecting it to be a minimum of 200 people and 20 acts. So it would have to be somewhat intelligent. Like I said though, I am sure I am not the only person that has needed to do something like this. I mean in the end, if the show was repeated twice, I was even wanting to make sure someone was scheduled first and last. I bet this is going to take multiple table relationships and some clever php code. I wish Steven Hawking visited these forums