Incrementing Count within a Group By

Question

I'm trying to get an incrementing counter within a set of results.

For example, assume I have a messages table:

messages
--------
- id (int)
- user_id (int)
- sent_at (date)
- body (text)

I'd like to perform a query that gives me results like this:

+---------+------------+-------------+---------+
| user_id | message_id | sent_at     | counter |
+---------+------------+-------------+---------+
|       1 |          1 |  2017-01-01 |       1 |
|       1 |          3 |  2017-01-15 |       2 |
|       1 |          4 |  2017-01-22 |       3 |
|       2 |          2 |  2017-01-06 |       1 |
|       2 |          6 |  2017-01-22 |       2 |
|       3 |          5 |  2017-01-22 |       1 |
|       3 |          7 |  2017-01-28 |       2 |
|       3 |          8 |  2017-02-03 |       3 |
|       3 |          9 |  2017-02-14 |       4 |
+---------+------------+-------------+---------+

Essentially, the counter increments exclusively within the user_id group, and each internal group is ordered by the sent_at column.

I know that I can easily get the first three columns with the following SQL:

SELECT
   user_id,
   id AS message_id,
   sent_at
FROM messages
ORDER BY
    user_id,
    sent_at

But I'm in need of that fourth count column.

I know that I can use ROW_NUMBER() to get the result row number:

SELECT
   user_id,
   id AS message_id,
   sent_at,
   ROW_NUMBER() OVER(ORDER BY user_id, sent_at) AS counter
FROM messages
ORDER BY
    user_id,
    sent_at

But that gives me the following results:

+---------+------------+-------------+---------+
| user_id | message_id | sent_at     | counter |
+---------+------------+-------------+---------+
|       1 |          1 |  2017-01-01 |       1 |
|       1 |          3 |  2017-01-15 |       2 |
|       1 |          4 |  2017-01-22 |       3 |
|       2 |          2 |  2017-01-06 |       4 |
|       2 |          6 |  2017-01-22 |       5 |
|       3 |          5 |  2017-01-22 |       6 |
|       3 |          7 |  2017-01-28 |       7 |
|       3 |          8 |  2017-02-03 |       8 |
|       3 |          9 |  2017-02-14 |       9 |
+---------+------------+-------------+---------+

If I could reset the counter somehow after each new user_id, I'd get the result that I'm looking for.

Answer 1

You need to simply use PARTITION BY:

SELECT
   user_id,
   id AS message_id,
   sent_at,
   ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY user_id, sent_at) AS counter
FROM messages
ORDER BY
    user_id,
    sent_at;

Answer 2

Using row_number is the right way to go. You're just missing a partition by clause to get a new counter for each distinct user_id:

SELECT
   user_id,
   id AS message_id,
   sent_at,
   ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY sent_at) AS counter
   -- Here ----------^
FROM messages
ORDER BY
    user_id,
    sent_at

Answer 3

You are looking for partition by:

SELECT user_id, id AS message_id, sent_at,
       row_number() over (partition by user_id order by sent_at) AS counter
FROM messages m
ORDER BY user_id, sent_at;