How to properly group SQL results set?

Question

SQL noob, please bear with me!!

I am storing a 3-tuple in a database (x,y, {signal1, signal2,..}).
I have a database with tables coordinates (x,y) and another table called signals (signal, coordinate_id, group) which stores the individual signal values. There can be several signals at the same coordinate.

The group is just an abitrary integer which marks the entries in the signal table as belonging to the same set (provided they belong to the same coordinate). So that any signals with the same 'coordinate_id' and 'group' together form a tuple as shown above.

For example,

Coordinates table                 Signals table
--------------------             -----------------------------     
| id  |  x  |  y  |              | id | signal | coordinate_id | group | 
|  1  |  1  |  2  |              | 1  |   45   |       1       |   1   |
|  2  |  2  |  5  |              | 2  |   95   |       1       |   1   |
                                 | 3  |   33   |       1       |   1   |
                                 | 4  |   65   |       1       |   2   |
                                 | 5  |   57   |       1       |   2   |
                                 | 6  |   63   |       2       |   1   |

This would produce the tuples (1,2 {45,95,33}), (1,2,{65,57}), (2,5, {63}) and so on.

I would like to retrieve the sets of {signal1, signal2,...} for each coordinate. The signals belonging to a set have the same coordinate_id and group, but I do not necessarily know the group value. I only know that if the group value is the same for a particular coordinate_id, then all those with that group form one set.

I tried looking into SQL GROUP BY, but I realized that it is for use with aggregate functions.

Can someone point out how to do this properly in SQL or give tips for improving my database structure.

Answer 1

SQLite supports the GROUP_CONCAT() aggregate function similar to MySQL. It rolls up a set of values in the group and concatenates them together comma-separated.

SELECT c.x, c.y, GROUP_CONCAT(s.signal) AS signal_list
FROM Signals s
JOIN Coordinates ON s.coordinate_id = c.id
GROUP BY s.coordinate_id, s.group

SQLite also permits the mismatch between columns in the select-list and columns in the group-by clause, even though this isn't strictly permitted by ANSI SQL and most implementations.

Answer 2

personally I would write the database as 3 tables:

x_y(x, y, id)  coords_groups(pos, group, id)  signals(group, signal)  
with signals.group->coords_groups.id and coords_groups.pos->x_y.id

as you are trying to represent a sort-of 4 dimensional array.

then, to get from a couple of coordinates (X, Y) an ArrayList of List of Signal you can use this

SELECT temp."group", signals.signal 
FROM (
    SELECT cg."group", cg.id
    FROM x_y JOIN coords_groups AS cg ON x_y.id = cg.pos
    WHERE x_y.x=X AND x_y.y=Y ) 
AS temp JOIN signals ON temp.id=signals."group"
ORDER BY temp."group" ASC

(X Y are in the innermost where)

inside this sort of pseudo-code:

getSignalsGroups(X, Y)
    ArrayList<List<Signals>> a
    List<Signals> temp
    query=sqlLiteExecute(THE_SQL_SNIPPET, x, y)

    row=query.fetch()        //fetch the first row to set the groupCounter
    actualGroup=row.group
    temp.add(row.signal)

    for(row : query)         //foreach row add the signal to the list
        if(row.group!=actualGroup)     //or reset the list if is a new group
            a.add(actualGroup, temp)
            actualGroup=row.group; temp= new List
        temp.add(row.signal)
    return a