> First, note that left join is a red herring here, since you don't in fact > have any records in T1 without a matching record in T2. > > Try this: > > select NAME, group_concat(COUNT), group_concat(TYPE) > from ( > select T1.NAME NAME, T2.COUNT COUNT, T2.TYPE TYPE > from T1 left join T2 on T1.ID=T2.REFID > order by T2.COUNT); > > Igor Tandetnik > >
Well, in fact, it's possible that a record in T1 doesn't have a matching record in T2. So I have to use left join. I'm sorry for not clearing that up. Thanks for the hint. It works, but I noticed the query takes much longer to complete (5 times actually in my smaller testing db). My real scenario is much more complicated and the performance is even worse. So, any alternatives? Can it be done without a sub-select? About performance, it is important in my scenario. That's why I used group_concat. I could use additional queries to fetch the data from T2. But I figured as long as it's done in a single query instead of multiple, the performance shall be better. Please correct me if I'm wrong here, because I'm not so sure if I'm heading in the right direction. Thanks, He Shiming _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
