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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to