Search code examples
iphoneiossqlsqlitefmdb

how to get grouped query data from the resultset?

I want to get grouped data from a table in sqlite. For example, the table is like below:

Name  Group  Price
a     1      10
b     1      9
c     1      10
d     2      11
e     2      10
f     3      12
g     3      10
h     1      11

Now I want get all data grouped by the Group column, each group in one array, namely 

array1 = {{a,1,10},{b,1,9},{c,1,10},{h,1,11}}; 
array2 = {{d,2,11},{e,2,10}}; 
array3 = {{f,3,12},{g,3,10}}.

Because i need these 2 dimension arrays to populate the grouped table view. the sql statement maybe NSString *sql = @"SELECT * FROM table GROUP BY Group"; But I wonder how to get the data from the resultset. I am using the FMDB.

Any help is appreciated.

Solution

  • Get the data from sql with a normal SELECT statement, ordered by group and name:

    SELECT * FROM table ORDER BY group, name;

    Then in code, build your arrays, switching to fill the next array when the group id changes.