## Saturday 26 January 2008

### RANK, DENSE_RANK AND ROW_NUMBER analytic functions

I wrote this test to better understand the use of RANK, DENSE_RANK and ROW_NUMBER Oracle analytic functions and Top-N querying. The beauty of this exercise is that all 3 analytic functions are illustrated with one example.

Suppose the business requirement in this case is to rank the products of each customer by the quantity purchased.

The test table I used for this is:
`CREATE TABLE t(customer_name VARCHAR2(50),product_name VARCHAR2(50),hits NUMBER)`

The data in the table is 16 rows like below:
`INSERT INTO t VALUES('bill', 'egg', 1);INSERT INTO t VALUES('bill', 'egg', 1);INSERT INTO t VALUES('bill', 'beer', 3);INSERT INTO t VALUES('bill', 'beer', 2);INSERT INTO t VALUES('bill', 'jeans', 1);INSERT INTO t VALUES('larry', 'beer', 4);INSERT INTO t VALUES('larry', 'beer', 2);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'olives', 1);INSERT INTO t VALUES('larry', 'olives', 1);INSERT INTO t VALUES('larry', 'car', 1);COMMIT;`

`SELECT * FROM t;CUSTOMER_NAME      PRODUCT_NAME            HITS------------------ ----------------- ----------bill               egg                        1bill               egg                        1bill               beer                       3bill               beer                       2bill               jeans                      1larry              beer                       4larry              beer                       2larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              olives                     1larry              olives                     1larry              car                        1`

The solution query

`SELECTcustomer_name,product_name,hits,rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) rank,dense_rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) denserank,row_number() over(PARTITION BY customer_name ORDER BY hits DESC) rownumberFROM(SELECTcustomer_name,product_name,SUM(hits) hitsFROM tGROUP BY customer_name, product_name);CUSTOMER_NAME   PRODUCT_NAME          HITS       RANK  DENSERANK  ROWNUMBER--------------- --------------- ---------- ---------- ---------- ----------bill            beer                     5          1          1          1bill            jeans                    1          2          2          2bill            egg                      1          2          2          3larry           cheese                   6          1          1          1larry           beer                     6          1          1          2larry           olives                   2          3          2          3larry           car                      1          4          3          47 rows selected.`

Observe how dense rank will not skip the ranking sequence when products with the same amount of hits are ranked with the same rank. In this case Larry's cheese and beer are ranked in rank 1 in the DENSERANK column and Larry's olives immediately after that are ranked with 2. Whereas the situation is quite different for this case in the RANK column where Larry's olives are ranked with 3.

## Thursday 24 January 2008

### Data Modeling with Oracle JDeveloper

Long time now I have been looking for a Free Data Modelling tool which is from the Oracle breed but is not part of a gigabytes size software installation (Discoverer...etc).

Finally oracle has bundled Data Modeling in JDeveloper which is a free tool they give for building Java applications. All this time JDeveloper hasn't been of my taste because of its name "the J". I am not a Java Developer! But I kind of started liking it now, when I checked it closely, I found out that it has changed a bit since the last time I had a look at it couple of years before, it is more DBA friendly.

JDeveloper is:

• Free!

• Smaller, compact sizes of 400M for a full installation

• Familiar interface to Oracle SQL Developer. (I didn't realise that Oracle SQL Developer actually was already part of JDeveloper all this time, I always thought that Oracle SQL Developer was a new tool, clever!)

• Capable of database development and more DBA friendly with SQL Sheet, ER Modeling, PL/SQL Editing

I would still prefer to see the Data Modeling part of it available in Oracle SQL Developer as well, as a DBA I want a mean and lean tool to do SQL, PL/SQL and ER Modeling.

For a download, and tutorials on how to create data models with JDeveloper go http://www.oracle.com/technology/obe/obe1013jdev/10131/database%20development/obe_%20dbasedevmt.htm