I have a table that looks like this
user_id | attr1 | attr2
--------+-------+-------
AAA | 123 | 345
BBB | 223 | 567
AAA | 345 | 477
CCC | 345 | 234
BBB | 356 | 123
CCC | 456 | 477
Clearly, the records can be grouped by user_id. What I want to do is batch/club records w.r.t user_id and then iterate over this group by retrieving all records under that user_id for further processing.
Something like after I group by user_id if want a list of lists. The inner list will contain separate record of the table and the outer list will contain list of al user_id.
So far I have come up with this -
SELECT DISTINCT user_id FROM table; =====> put this in a list
iterate over the above list and execute below query
SELECT * FROM table where user_id = list[i]
I feel there must be a better way to do this. I am writing the boilerplate code in Java and this is a Drill query if that helps.
How about ordering the table by user_id and just retrieving the rows one at a time?
SELECT t.*
FROM table t
ORDER BY user_id;
In the application, you can detect when the user_id changes for the processing.