Ranking/Counting Rows based on Column Value
analytic-functions, oracle, sql
Solution
It looks like you want the analytic function `rank`
SELECT uid, timestamp, rank() over (partition by uid order by timestamp) rnk
FROM your_table
If there can be ties (two rows with the same `UID` and `Timestamp`) then, depending on how you want to handle ties, you may want the `dense_rank` or `row_number` analytic function instead.
Problem
I have some data in a format described in this sqlfilddle: http://sqlfiddle.com/#!4/b9cdf/2 Basically, a table with a user ID and a time that an event occured. What I'd like to do is count the events based on the time they happened by user. So, a table that looks like: ``` UID Timestamp ----------------- 01 01-APR-12 01 01-APR-12 02 02-APR-12 03 05-APR-12 01 10-APR-12 02 11-APR-12 03 15-APR-12 01 20-APR-12 ``` I want this to assign a numerical rank to the events based on the order in which they occurred. So, this means the table above would become: ``` UID Timestamp Rank -------------------------- 01 01-APR-12 1 02 02-APR-12 1 03 05-APR-12 1 01 10-APR-12 2 02 11-APR-12 2 03 15-APR-12 2 01 20-APR-12 3 ``` Is there a way to do this in Oracle SQL? Or do I have to spit it out to a scripting language and take it from there? Thanks!