Sqlite Android order by & group_concat

Question

I'm using the sqlite3 : 3.8.2 with sqliteman (Ububuntu 14.04) to try the query that I need in my program Android.

Android version 4.0+.

I have 3 table to use to obtain data

I found this problem:

when I use this query in sqliteman

SELECT st.nome
AS nome,
st.cognome
AS cognome, 
(SELECT ae.usercode
AS usercode
FROM assenze ae
WHERE st.ultimo_evento = ae.fk_evento)
AS ultimo_evento,
(SELECT 
    GROUP_CONCAT(
        ev.nome_evento, ", "
    )
    AS nome_evento 
    FROM eventi ev 
    WHERE ev.studente_id = st.id 
)
AS nome_evento
FROM studenti st 
WHERE st.classe_ident ='4297'
ORDER BY st.cognome

the output is:

EP, USC, R1, R, A ... perfect for me

but when I use it in android with db.rawQuery() the result is

A, EP, R, R1, USC ... no good for me

also seems to be the same result of this query (execute in sqliteman and android):

SELECT 
st.nome AS nome, 
st.cognome AS cognome, 
ae.usercode AS ultimo_evento, 
GROUP_CONCAT(
   ev.nome_evento, ", "
) 
AS nome_evento
FROM studenti st
LEFT JOIN eventi ev
ON st.id = ev.studente_id
LEFT JOIN assenze ae
ON st.ultimo_evento = ae.fk_evento
WHERE st.classe_ident ='4297'
GROUP BY st.id
ORDER BY st.cognome

Probably it's my fault but I'm still trying to find a solution...

Thanks

seems that it works also without ORDER BY ev.data_ora_evento. But in android still not working...

Possible duplicate of [Sqlite group\_concat ordering](https://stackoverflow.com/questions/1897352/sqlite-group-concat-ordering) — szmate1618, Jul 17 '19 at 13:18

score 0 · Accepted Answer · answered Apr 21 '15 at 10:35

0

The group_concat() documentation says:

The order of the concatenated elements is arbitrary.

However, you could try to force an order by executing group_concat() on a subquery that is sorted:

SELECT GROUP_CONCAT(ev.nome_evento, ", ") AS nome_evento 
FROM (SELECT nome_evento
      FROM eventi
      WHERE studente_id = st.id
      ORDER BY whatever) AS ev

answered Apr 21 '15 at 10:35

CL.

173,858
17
217
259

thanks for the link, but if it is arbitrary why in sqliteman work as would and in Android not? – ad3luc Apr 21 '15 at 10:51
1

"Arbitrary" means that any order is correct. Different versions can use different optimizations. – CL. Apr 21 '15 at 10:53
so the sqliteman give me the correct output only for luck... Thanks The only way is to correct the order as I need when I obtain the string from the cursor... – ad3luc Apr 21 '15 at 11:00

Sqlite Android order by & group_concat

1 Answers1