Thread: Number timestamped rows
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 How would I do this with an UPDATE statement (e.g.) in pgsql? Thanks in advance for any hint Jan -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone
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°
On 2011-11-02, Jan Peters <petersjan@gmx.at> wrote: > Dear all, > maybe a stupid question, but: I have a table that is ordered like this: > Tables aren't ordered. Sometimes they may seem to be ordered, but they seldom stay that way for long. > and I would like to number them according to their timestamps like this: > How would I do this with an UPDATE statement (e.g.) in pgsql? If you want ordered data use an ORDER BY clause in the select, there is no other reliable way. -- ⚂⚃ 100% natural