SQL group by without aggregation

Question

I'm using PostgreSQL, and I have the following table (simplified for the question):

CREATE TABLE SMSNotification (
    enduser int,
    message int,
    FOREIGN KEY (enduser) REFERENCES EndUser (id),
    FOREIGN KEY (message) REFERENCES Message (id)
);

Now, I'd like to batch send the messages. Hence, I'd like to group the notifications by message. The problem is that the GROUP BY operator in SQL aggregates rows. Therefore, I can't get the list of end users in each group:

SELECT enduser, message from SMSNotification GROUP BY message; /* Doesn't work */

I thought of solving this problem by first getting the list of all distinct messages:

SELECT DISTINCT message FROM SMSNotification;

Then for each message, getting the list of end users:

 SELECT enduser FROM SMSNotification WHERE message = current_message;

However, this seems inefficient because of the N+1 selects. Is there a better solution?

---

Example: Consider the following table:

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 1       |
| 2       | 1       |
| 3       | 1       |
| 1       | 2       |
| 2       | 2       |
| 1       | 3       |
| 3       | 3       |
+---------+---------+

Ideally, I'd like to get a list of tables as an output:

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 1       |
| 2       | 1       |
| 3       | 1       |
+---------+---------+

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 2       |
| 2       | 2       |
+---------+---------+

+---------+---------+
| enduser | message |
+---------+---------+
| 1       | 3       |
| 3       | 3       |
+---------+---------+

However, if I understand correctly, you can't get a list of tables as an output from an SQL query.

Answer 1

I think you are looking for partition by command (a window function):

select row_number() over (partition by message order by enduser)
    as rn
    , message
    , enduser
from SMSNotification

A group by reduces the number of rows returned, by aggregating the non-grouped columns, while partition by does not affect the number of rows, it just specifies how the output must be displayed or calculated.

Answer 2

You could aggregate the users into an array like in

SELECT array_agg(enduser) AS endusers, message
FROM SMSNotification
GROUP BY message;

 endusers | message 
----------+---------
 {3}      |       3
 {2,3}    |       2
 {1,3}    |       1
(3 rows)