Monthly Archives: November 2013

RANK & DENSE_RANK –(Oracle SQL-PLSQL)

Reference : http://www.techhoney.com/oracle/function/dense_rank-function-in-oracle-sql-plsql/

 

DENSE_RANK Function

The Dense_Rank function in Oracle SQL / PLSQL is used to return the rank or position of a value in a group of values. It’s very similar to RANK function but RANK function can cause non-consecutive rankings if the tested values are same.

DENSE_RANK function can be used in as an Aggregate and Analytical Function.

Syntax for the DENSE_RANK function as an Aggregate Function in Oracle SQL / PLSQL is:

SELECT DENSE_RANK(expression1, expression2, . . , expressionN)
WITHIN GROUP (ORDER BY column1, column2, . . , columnN)
FROM table_name;

Syntax for the DENSE_RANK function as an Analytical Function in Oracle SQL / PLSQL is:

SELECT DENSE_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

The number of expressions the DENSE_RANK function and ORDER BY clause must be the same and also the data types should be compatible.

Example 1:

Using DENSE_RANK as AGGREGATE function

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

If we write our query as:

SELECT DENSE_RANK(15000) WITHIN GROUP (ORDER BY salary)
FROM employee;

Will return ‘2’ as the rank of 15000 because as per the data in the ‘employee’ table if we sort the ‘salary’ column from lowest to highest salary then 15000 will come at the 2nd position in that list.


Example 2:

Using DENSE_RANK as ANALYTICAL function

Syntax for the DENSE_RANK function as an Analytical Function in Oracle SQL / PLSQL is:

SELECT DENSE_RANK() OVER ([PARTITION BY column(s)] ORDER BY column(s))
FROM table_name;

Suppose we have a table named ‘employee’ as shown below:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

If we write our query as:

SELECT employee_name
       ,salary
       ,department
       ,DENSE_RANK() OVER (PARTITION BY department ORDER BY salary) Dense_Ranking
FROM employee;
Employee_Name Salary Department Dense_Ranking
Emp B 20000 IT 1
Emp C 28000 IT 2
Emp A 10000 Sales 1
Emp E 32000 Sales 2
Emp D 30000 Support 1

Here we can see that DENSE_RANK function is being used as an analytical function and it returns the position or rank of records in group of records partitioned by a criteria e.g. in our case we have partitioned the records by ‘department’ and then within that partition we are ranking the records relative to each other.

‘Emp B’ is having a rank of 1 and ‘Emp C’ is having a rank 2 in ‘IT’ partition because we have sorted the list by ‘Salary’ and ‘Emp B’s salary’ is less than ‘Emp C’s salary’ within the ‘IT’ partition.

Similarly with in ‘Sales’ partition ‘Emp A’ is having less salary than ‘Emp E’ that’s why ‘Emp A’ is having rank 1 and ‘Emp E’ having rank 2.

Advertisements

Primary Key – With / Without

Answer One:

Should each and every table have a primary key?

Short answer: yes.

Long answer:

  • You need your table to be joinable on something
  • If you want your table to be clustered, you need some kind of a primary key.
  • If your table design does not need a primary key, rethink your design: most probably, you are missing something. Why keep identical records?

In MySQL, the InnoDB storage engine always creates a PRIMARY KEY if you didn’t specify it explicitly, thus making an extra column you don’t have access to.

Besides the logical consistency issues, most RDBMS engines will benefit from including these fields in anUNIQUE index.

Answer Two :

Always best to have a primary key. This way it meets first normal form and allows you to continue along the database normalization path.

if you don’t have a primary key then you are going to have duplication of records somewhere in your table.

if you don’t have an index, BUT a primary key gets an index by default

Use Cases : a table not to have a primary key?

I tend to agree that most tables should have a primary key. I can only think of two times where it doesn’t make sense to do it.

  1. If you have a table that relates keys to other keys. For example, to relate a user_id to an answer_id, that table wouldn’t need a primary key.
  2. A logging table, whose only real purpose is to create an audit trail.