Re: Number timestamped rows

Jan Peters <petersjan(at)gmx(dot)at> wrote:

> Dear all,
> maybe a stupid question, but: I have a table that is ordered like this:
>
> user_id|timestamp|event
> 1 |0:1 |event_a
> 1 |0:2 |event_b
> 2 |0:1 |event_b
> 2 |0:3 |event_c
> 2 |0:4 |event_b
> 3 |0:1 |event_a
>
> and I would like to number them according to their timestamps like this:
>
> user_id|timestamp|event |order
> 1 |0:1 |event_a |1
> 1 |0:2 |event_b |2
> 2 |0:1 |event_b |1
> 2 |0:3 |event_c |2
> 2 |0:4 |event_b |3
> 3 |0:1 |event_a |1
>

untested:

select user_id, timestamp, event, row_number() over (partition by
user_id order by timestamp) as "order" from your_table

> How would I do this with an UPDATE statement (e.g.) in pgsql?

I think, you don't need an UPDATE, just a SELECT.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°