Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

RANK Function in Oracle SQL – PLSQL

by

The 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 DENSE_RANK function but RANK function can cause non-consecutive rankings if the tested values are same.

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

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

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

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

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

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

Example 1:

Using 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 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 RANK as ANALYTICAL function

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

SELECT 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
       ,RANK() OVER (PARTITION BY department ORDER BY salary) Ranking
FROM employee;
Employee_Name Salary Department 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 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.

ROWNUM Pseudo Column in Oracle SQL – PLSQL

by

The ROWNUM is a pseudo column in Oracle SQL / PLSQL which returns a row’s position in the fetched result set.
ROWNUM is evaluated AFTER records are SELECTED from the data-base and BEFORE execution of the ORDER BY clause.

Syntax for the ROWNUM function in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE ROWNUM < n;

Example 1:
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 out query as:

SELECT *
FROM employee
WHERE ROWNUM <3;

We will get the following result:

Employee_Id Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20

Here we can see that the PSEUDO COLUMN ROWNUM has restricted the number of rows in the fetched result to 2.

However ROWNUM always returns no data when used as:
WHERE ROWNUM > n
Or
WHERE ROWNUM BETWEEN n AND m
Or
WHERE ROWNUM IN(a,b,c…n)

HEXTORAW Function in Oracle SQL – PLSQL

by

The HEXTORAW function in Oracle SQL / PLSQL is used to convert hexadecimal values to raw values.

Syntax for the HEXTORAW function in Oracle SQL / PLSQL is:

SELECT HEXTORAW(hexa_decimal_value)
FROM table_name;

  • hexa_decimal_value is the hexadecimal value that is to be converted to raw value.

Example 1:

SELECT HEXTORAW('90A')
FROM DUAL;

Will return ‘090A’ as raw value.

Example 2:

SELECT HEXTORAW('00F')
FROM DUAL;

Will return ‘000F’ as raw value.

COALESCE Function in Oracle SQL – PLSQL

by

The COALESCE function in Oracle SQL / PLSQL is used to return the first NOT NULL expression in the list.

Syntax for the COALESCE function in Oracle SQL / PLSQL is:

SELECT COALESCE(expresion1, expression2, expression3, . . , expressionN)
FROM table_name;

  • expression1 to expressionN are expressions to be tested for NULL values

Let’s take an example for understanding:
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

Example 1:

If we write our query as:

SELECT COALESCE(employee_id
       ,salary
       ,commission)
FROM employee;

The output of the above statement will be:

COALESCE(EMPLOYEE_ID,SALARY,COMMISSION)
101
102
103
104
105

The above coalesce query is equivalent to writing:

IF employee_id is NOT NULL THEN
  Result = employee_id
ELSE IF salary is NOT NULL THEN
  Result = salary
ELSE IF commission is NOT NULL THEN
  Result = commission
ELSE
  Result = NULL
END IF

Example 2:
Suppose we have employee table as:

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 Support
105 Emp E Sales 10

If we write our query as:

SELECT COALESCE(commission
       ,salary
       ,employee_id)
FROM employee;

The output of the above statement will be:

COALESCE(COMMISSION,SALARY,EMPLOYEE_ID)
10
20
28000
104
105

Here we can see that for employee_id = 104’ and ‘105’, the coalesce query has fetched ‘employee_id’ because ‘commission’ and ‘salary’ records for both these employees are having NULL values.

The above coalesce query is equivalent to writing:

IF commission is NOT NULL THEN
  Result  = commission
ELSE IF salary is NOT NULL THEN
  Result  = salary
ELSE IF employee_id is NOT NULL THEN
  Result = employee_id
ELSE
  Result  = NULL
END IF

TRANSLATE Function in Oracle SQL – PLSQL

by

The TRANSLATE function in Oracle SQL / PLSQL is used to replace a sequence of characters in a string with another sequence of characters.

The translate function replaces character by character i.e. it will replace the first character in the string to be replaced by the first character in the replacing string.

Syntax for the TRANSLATE function in Oracle SQL / PLSQL is:

SELECT TRANSLATE(string1, string_to_replace, replacement_string)
FROM table_name;

  • String1 is the string in which sequences of characters are to be replaced
  • String_to_replace is the sequence of characters to be replaced
  • Replacement_string is the string of characters that will replace the string_to_replace

Let’s take an example for understanding:

SELECT TRANSLATE('Tech Honey', 'ech','abc')
FROM dual;

The output of the above statement will be:

TRANSLATE(‘TECHHONEY’,’ECH’,’ABC’)
Tabc Honay

Here we can see that ‘e’ in ‘Tech Honey’ is replaced by ‘a’ in ‘abc’, ‘c’ is replaced by ‘b’ and ‘h’ is replaced by ‘c’.