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.

Example
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).

select
user_id,
to_char(time,'YYYY-MM-DD HH24:MI:SS') LOGON_TIME
from
(
select
user_id,
time,
rank() over(partition by user_id
order by time asc) rowrank
from
logins
)
where
rowrank=1
/
Returns
   USER_ID LOGIN_TIME
---------- -------------------
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:
http://www.adp-gmbh.ch/ora/sql/analytical/rank.html
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions123.htm

Im working on the second part of the XML stuff.

1 comment:

highheels 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.