Imagine a table that holds individual lines of text documents:
CREATE TABLE DocLines
(
DocID INTEGER,
LineIndex INTEGER,
LineText TEXT
);
INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (1, 1, 'Mary had a
little lamb');
INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (1, 2, 'It had white
fleece');
//inserted in reverse order so insertion order doesn't happen to make the
SELECT work
INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (2, 2, 'Humpty dumpty
had a great fall');
INSERT INTO DocLines (DocID, LineIndex, LineText) VALUES (2, 1, 'Humpty dumpty
sat on a wall');
How can I select a document and get the complete sorted text back in a single
row (so I can do a JOIN on a different table with additional information)?
The below happens to work fine for DocID 1 (because of insertion order), but
fails for DocID 2.
SELECT group_concat(LineText, '\n')
FROM DocLines
WHERE DocID = 1
GROUP BY DocID;
Is there any way to order a GROUP BY, or some other way to concatenate text?
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
