> SELECT group_concat(fieldB,' ') FROM myTable WHERE fieldA = 'val1' ORDER BY 
> rowid
>
> Might get one of the values you wanted.  One concern is that I don't remember 
> whether the ORDER BY clause is obeyed before or after aggregation.

It's obeyed after aggregation. So the same query with GROUP BY won't
work. Although one might write something like this:

SELECT a.fieldA,
(SELECT group_concat(b.fieldB, ' ') FROM myTable b
  WHERE a.fieldA = b.fieldA
  ORDER BY b.rowid)
FROM myTable a
GROUP BY a.fieldA


Pavel


On Tue, May 29, 2012 at 1:33 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 29 May 2012, at 6:24pm, Gert Van Assche <ger...@datamundi.be> wrote:
>
>> I have a dataset that looks like this:
>>
>> rowid ; fieldA ; fieldB
>> 1     ; val1   ; This is a
>> 2     ; val1   ; small
>> 3     ; val1   ; test.
>> 4     ; val2   ; The proof is in
>> 5     ; val2   ; the pudding.
>>
>>
>> And I would like to merge all values in fieldB when the value in fieldA is
>> the same.
>>
>> rowid ; fieldA ; fieldB
>> 1     ; val1   ; This is a small test.
>> 2     ; val2   ; The proof is in the pudding.
>>
>>
>> Question: can you do this with an SQL query?
>
> I haven't tried this but I'd try using the group_concat() function.  For 
> instance
>
> SELECT group_concat(fieldB,' ') FROM myTable WHERE fieldA = 'val1' ORDER BY 
> rowid
>
> Might get one of the values you wanted.  One concern is that I don't remember 
> whether the ORDER BY clause is obeyed before or after aggregation.
>
> But if the above works the way you want, then try and get all the values you 
> want in one SELECT using GROUP BY.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to