If line_n and word_position (together) are not unique, how in the world do you build the string? Well, unless id breaks the ties.
If you want to see the work in progress, the recursive query is even easier. (No MAX in the anchor, and no WHERE clause is the main query.)
| [reply] |
I'm not a software developer and maybe I miss something but the SQL solution I normally work with is:
SELECT line_n,
GROUP_CONCAT(word ORDER BY id SEPARATOR ' ')
FROM myTable
WHERE myText = 'something'
GROUP BY line_n
ORDER BY id
In fact it is governed by the id | [reply] |
GROUP_CONCAT is an RDBMS-specific function. Some others don't have it, some do, and then under other names.
Anyway, the use of id is redundant and, for that matter, probably wrong, assuming i'm understanding the layout correctly.
line_n groups all words in any given line together. word_position defines their order. id is the order in which they are inserted into the table. As it so happens, lines and words are inserted in order so id can be used as a surrogate for word_position to define the order. However, should a word be inserted out of order, a word_position be updated, or (a rare cases that applies only in some situations) the ids be reused, the usage of id may produce incorrect results. In any case, line_n and word_position will always be correct.
The recursive query above should work. It has been modified to squelch the non-finished lines (via the MAX in the anchor). If the in-progress lines are desired, it is a simple modification.
| [reply] |
You haven't shown any myText column in the table sample.
($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
| [reply]
[d/l]
[select] |