Oracle / PLSQL: DENSE_RANK Function

This Oracle tutorial explains how to use the Oracle/PLSQL DENSE_RANK function with syntax and examples. 

Description 

The Oracle/PLSQL DENSE_RANK function returns the rank of a row in a group of rows. It is very similar to the RANK function. However, the RANK function can cause non-consecutive rankings if the tested values are the same. Whereas, the DENSE_RANK function will always result in consecutive rankings. 

The DENSE_RANK function can be used two ways - as an Aggregate function or as an Analytic function. 

DENSE_RANK Function Syntax #1 - Used as an Aggregate Function 

As an Aggregate function, the DENSE_RANK function returns the dense rank of a row within a group of rows. 

The syntax for the DENSE_RANK function when used as an Aggregate function is: DENSE_RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )

Parameters or Arguments expression1 .. expression_n One or more expressions which identify a unique row in the group. 

Returns 

The DENSE_RANK function returns a numeric value. 

Note 

There must be the same number of expressions in the first expression list as there is in the ORDER BY clause. 

The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause. 

Applies To 

The DENSE_RANK function can be used in the following versions of Oracle/PLSQL: 

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i 

Example (as an Aggregate Function) 

Let's look at some Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL. 

For example: 

select DENSE_RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) 

from employees;

The SQL statement above would return the dense rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table. 

DENSE_RANK Function Syntax #2 - Used as an Analytic Function 

As an Analytic function, the DENSE_RANK function returns the rank of each row of a query with respective to the other rows. 

The syntax for the DENSE_RANK function when used as an Analytic function is: DENSE_RANK() OVER ( [ query_partition_clause] ORDER BY clause )

Returns 

The DENSE_RANK function returns a numeric value. 

Applies To 

The DENSE_RANK function can be used in the following versions of Oracle/PLSQL: 

Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i 

Example (as an Analytic Function) 

Let's look at some Oracle DENSE_RANK function examples and explore how to use the DENSE_RANK function in Oracle/PLSQL. 

For example: 

select employee_name, salary, 

DENSE_RANK() OVER (PARTITION BY department ORDER BY salary) 

from employees where department = 'Marketing';

The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the DENSE_RANK function would return the same rank for both employees.

Oracle / PLSQL: RANK Function

This tutorial explains how to use the Oracle/PLSQL RANK function with syntax and examples.

Description

The Oracle/PLSQL RANK function returns the rank of a value in a group of values. It is very similar to the DENSE_RANK function. However, the rank function can cause non-consecutive rankings if the tested values are the same. Whereas, the DENSE_RANK function will always result in consecutive rankings.

The RANK function can be used two ways - as an Aggregate function or as an Analytic function.

RANK Function Syntax #1 - Used as an Aggregate Function

As an Aggregate function, the RANK function returns the rank of a row within a group of rows.

The syntax for the RANK function when used as an Aggregate function is:

RANK( expr1 [, expr2, ... expr_n ] ) WITHIN GROUP ( ORDER BY expr1 [, expr_2, ... expr_n ] )

Parameters or Arguments expr1 

First expression which identifies a unique row in the group. expr2, ... expr_n 

Optional. Additional expressions which identifies a unique row in the group. 

Returns

The RANK function returns a numeric value.

Note 

There must be the same number of expressions in the first expression list as there is in the ORDER BY clause. 

The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause. 

Applies To

The RANK function can be used in the following versions of Oracle/PLSQL: 

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i 

Example (as an Aggregate Function)

Let's look at some Oracle RANK function examples and explore how to use the RANK function in Oracle/PLSQL.

For example:

select RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) 

from employees;

The SQL statement above would return the rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

RANK Function Syntax #2 - Used as an Analytic Function

As an Analytic function, the RANK function returns the rank of each row of a query with respective to the other rows.

The syntax for the RANK function when used as an Analytic function is:

rank() OVER ( [ query_partition_clause] ORDER BY clause )

Returns

The RANK function returns a numeric value.

Applies To

The RANK function can be used in the following versions of Oracle/PLSQL: 

Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i 

Example (as an Analytic Function)

select employee_name, salary, 

RANK() OVER (PARTITION BY department ORDER BY salary) 

from employees where department = 'Marketing';

The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the RANK function would return the same rank for both employees. However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is quite different from the DENSE_RANK function which generates consecutive rankings.

Oracle / PLSQL: ORA-01858 Error Message

ORA-01858: a non-numeric character found where a digit was expected

Cause

You tried to enter a date value using a specified date format, but you entered a non-numeric character where a numeric character was expected.

Sometimes in working with database software, matters of syntax and formatting can overlap to create a plethora of problems for users. Some platforms may require only numerical digits in certain sections. Others may need the month in a date written out in full and phonetic form.

Despite the needs of the program, it would be impossible for any one individual to strictly adhere to the parameters of Oracle on every occasion. The ORA-01858 can be considered as a warning to users are jumping between various tables and data sets at a quick pace and are letting the formatting concerns slip through the cracks.

The Problem

The ORA-01858 error is an issue of syntax and formatting. When prompted with the error, the user will receive an accompanying message that will state that “a non-numeric character was located where a digit was expected”. Oracle docs list the cause of this error as “the input data to be converted using a date format model being incorrect. The input data did not contain a number where a number was required by the format model.”

What does this mean in simple terms? This error essentially occurs when a user attempts to convert a string of data into a date, and in doing so specified a date being passed in a particular format. This will most commonly be something along the lines of DD-MM-YYYY. The error pops up when the user then tries to pass the date in the DD-MON-YYYY format. In such a case, a character-stated month such as “JAN” for January or “JUL” for July, will cause the error by inserting an alphabetical name in a place that Oracle expects to read a numerical name (such as “01” for January or “07” for July). Because this error is prompted by a character presence in a numerically-determined field, the most likely culprit will be a date entered in a format that writes the month specifically out in some form.

The Solution

In order to solve this formatting issue, there are basically two options to take. The user can either fix the input data to conform to the format in question, or the user can switch the date format model to ensure that the elements match in number and type and then retry the operation. In most cases, the former will be the simpler strategy.

Before we continue, it would be important to note that if a user is attempting to fetch strings from a table and subsequently convert them into dates, the data in the table should be checked prior to using the date format string. If the table contained strings that are note actually date values and the user attempts to convert them, then this error could be raised (although this is less common).

Since there is no predefined exception for handling this specific error, let us look at an example of a user-defined way of addressing it. In this scenario, the default date format is set to DD-MON-YY and the following statement was executed:

SQL> select to_date(’10-JUN-2014’, ‘DD-MM-YYYY’) from dual;

ERROR:

ORA-01858: a non-numeric character was found where a numeric was expected

no rows selected

At this juncture, after assessing the data and determining that the date information needs to be adjusted, the following can be run:

SQL> select to_date(’10-JUN-2014’, ‘DD-MON-YYYY’) DT from dual;

   DT

10-JUN-2014

This should clear up the hypothetical date in question and allow the statement to run smoothly.

Oracle 12c New Features " APPROX_COUNT_DISTINCT "

Have you ever been investigating a table and wanted to find out how many records it contains with a simple SELECT COUNT(*) query, but the query took a long time to run? Or, if you wanted to find the number of distinct values in a column, but the query was slow?

Well, we now have an easier way to run this query.

A new function, called APPROX_COUNT_DISTINCT. This lets you find the approximate number of distinct values in a column.

It's called an approximate because it doesn't give you the exact number. But, the number is pretty close.

Hide Copy Code

SELECT COUNT(DISTINCT first_name) FROM student; 

SELECT APPROX_COUNT_DISTINCT(first_name) FROM student;

The first query uses the actual COUNT function, and the second uses the new function. The second query should run a lot faster and get a number that is pretty close to the correct one.

Oracle 12c New Features " Grant Roles to PL/SQ Programs Only "

In Oracle 11g and earlier versions, whenever you wanted to run a PL/SQL program that accessed a table, you needed to grant access to the user running the program both to the program and the underlying table.

This felt a bit strange, because the package was the one accessing the table. And whenever you adjusted the package, you needed to adjust the access to the users.

In Oracle 12c, you can just give access to the user to the PL/SQL package, and not the underlying tables. This makes it easier to administrate and better for security.

Oracle 12c New Features " Period Definition "

Oracle 12c allows you to easily define when a record is effective from. Before having this feature, it was often done using start and end dates, or an effective date, in the database table.

Now, when you create a table, you add a PERIOD clause:

Hide Copy Code

CREATE TABLE student ( 

student_id NUMBER,

first_name VARCHAR2(50), 

last_name VARCHAR2(50), 

start_date DATE, 

end_date DATE, PERIOD FOR VALID (start_date, end_date));

This PERIOD clause refers to two columns, which are the start and end date. Now, you'll still need these columns, but it makes your queries a lot easier.

Hide Copy Code

SELECT student_id, 

       first_name, 

       last_name, 

       start_date, 

       end_date 

FROM student 

AS OF PERIOD FOR VALID sysdate;

You add in the AS OF PERIOD FOR VALID, and then a date. The date can be any value you like, and the records returned will be the ones where the date is between the start and end date.

It's a bit of an improvement to a process that I've seen quite often.

Oracle 12c New Features " Identity Columns "

A column in a table can be marked as identity column which generates its value by itself. Oracle implicitly creates a sequence of default configuration for the identity column. For each insert operation, the current value of the sequence gets automatically assigned to the identity column. 

The feature syntax is as below.

SQL> create table t_id_col ( x number

generated by default as identity

( start with 10 increment by 15 ) primary key,

y varchar2(30))

/

Table created.

SQL> insert into t (x,y) values ( 1, ‘hello1’ );

1 row created.

SQL> insert into t (x,y) values ( default, ‘hello2’ );

1 row created.

SQL> insert into t (y) values ( ‘hello3’ );

1 row created.

SQL> select * from t;

    X          Y

—————————— ———————————

    1        hello1

    10       hello2

    25       hello3