Increasing the number in the group by
I am trying to get an incremental counter within a result set.
For example, suppose I have a table messages
:
messages
--------
- id (int)
- user_id (int)
- sent_at (date)
- body (text)
I would like to execute a query that will give 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 | 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 only increments within the group user_id
, and each inner group is ordered by a column sent_at
.
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 need this fourth column count
.
I know that I can use ROW_NUMBER()
to get the line number of the result:
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 it 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 one user_id
, I would get the result I'm looking for.
source to share
Usage row_number
is the right way. You're just missing a suggestion partition by
to get a new counter for each individual 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
source to share