Geek asks and answers

Row / Count Rows Based on Column Value

I have some data in the format described in this sqlfilddle: http://sqlfiddle.com/#!4/b9cdf/2

In principle, a table with a user ID and the time when the event occurred. What I would like to do is to count events based on the time that they made by the user. So, a table that looks like this:

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 events based on the order in which they occurred. Thus, this means the following table:

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 need to spit it out into the scripting language and take it from there?

Thank!

+3
source share
2 answers

, , rank

SELECT uid, timestamp, rank() over (partition by uid order by timestamp) rnk
  FROM your_table

( UID Timestamp), , , dense_rank row_number.

+3

- , RANC, DENSE_RANK, ROW_NUMBER MSSQL.

-1

More articles:


All Articles