Search code examples
sqloracle-databaseanalytic-functions

Ranking/Counting Rows based on Column Value

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!

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.