Thursday, May 18

Ranking rows in Oracle

Oracle has a very nifty function to rank the usefullness of a row, it is sort of comparable to an aggregate but returns an rank value for the input instead of just the result row.
Perhaps you want to find the 5 most sold items for every month of the year or you need to figure out who worked the longest hours from a timesheet system. The rank query returns a sequence over the "top ranked" rows as ordered by the rank queried. If you just want the top 1 you just select the whole rank statement as an identifier and add "where identifier=1" in your outer where statement.

I wrote this mockup query to show how it can be used.
We have a nice web based application where we, for audit purposes, store all applications logins to a logins table in the database. Lets say that you for some unknown reason need to figure out who was the first user to login each month (yes I know, terrible use case).

to_char(time,'YYYY-MM-DD HH24:MI:SS') LOGON_TIME
rank() over(partition by user_id
order by time asc) rowrank
---------- -------------------
68 2006-02-01 00:07:04
68 2006-03-01 00:07:17
68 2006-04-01 03:21:17
108 2006-05-01 00:00:13

More reading:

Im working on the second part of the XML stuff.

1 comment:

Anonymous said...

Christian Louboutin
As a way to design and style remarkably one of a kind and effective Christian Louboutin Petal Sandals; they consider benefit of your very last equipment and technologies in order that you’ll be fully capable of get the brightest and designer sandal designs from them in way. Seems wise, the Christian Louboutin Petal Sandals are extremely inimitable and flexible shoes in an attempt to seize your attentions directly. On the subject of the colours, the monumental fascination is that they use superlative colors for making fancy your Christian Louboutin Petal Sandals.