0

I have the following script

http://sqlfiddle.com/#!2/45a17/14

How it works is that it gets a selection of document numbers and groups them together into collections, these collections are then collected in a loop and prepared statements are generated to output to files by taking the collection and using it in an IN clauses, in the example provided i have created 3 collections of ids so i'll end up creating 3 files (the actual number is calculated but that's irrelevant to my problem).

However, the problem is that even if i order the the source the sequence is going to be broken, ie. for 1 file the ordered sequence will be 1,4,7,10... etc. rather than 1,2,3,4 etc.

now to be honest i don't mind how it's came out but someone does and has requested the sequence not be broken up, how can i alter my subselect in the final query so that the document numbers retain their sequence (given the results, document numbers 1-9 should be in one collection than 10-18 then 19-25)

NOTE: the query before it shows what the subselect outputs

Memor-X
  • 2,870
  • 6
  • 33
  • 57
  • 1
    You can use `ORDER BY` inside GROUP_CONCAT http://stackoverflow.com/questions/995373/mysql-sort-group-concat-values – Uriil Jun 11 '14 at 06:21
  • @Uriil that only orders the sequence but the sequence is still broken, so it's still outputting 1,4,7,10 rather than 1,2,3,4. i've edited the page in my link to use order by to show the sequence is still broken – Memor-X Jun 11 '14 at 06:32

1 Answers1

1
SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT i,FLOOR(i/3)+1 a,i % 3 + 1 b FROM ints;
+---+------+------+
| i | a    | b    |
+---+------+------+
| 0 |    1 |    1 |
| 1 |    1 |    2 |
| 2 |    1 |    3 |
| 3 |    2 |    1 |
| 4 |    2 |    2 |
| 5 |    2 |    3 |
| 6 |    3 |    1 |
| 7 |    3 |    2 |
| 8 |    3 |    3 |
| 9 |    4 |    1 |
+---+------+------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57