Re: Number timestamped rows

Jan Peters <petersjan@gmx.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°