Tech Honey

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

FIRST_VALUE Function with PARTITION BY Clause in Oracle SQL – PLSQL

by

The FIRST_VALUE Function in Oracle SQL / PLSQL is an analytical function and is used to return the first value in an ordered set of values.

  1. If the first value in the ordered set is NULL, then FIRST_VALUE function returns NULL unless we specify IGNORE NULLS.
  2. If we specify IGNORE NULLS then, FIRST_VALUE function returns the first NON NULL value in the ordered list, or NULL if the list contains all the NULL values.
  3. FIRST_VALUE function can be used with and without PARTITION BY clause.

Syntax for using the FIRST_VALUE function in Oracle SQL / PLSQL is:
SELECT column(s)
,FIRST_VALUE(column [IGNORE NULLS]) OVER ([PARTITION BY column] ORDER BY column ROWS UNBOUNDED PRECEDING)
FROM table_name;

Example 1:

Using FIRST_VALUE Function with PARTITION BY and without IGNORE NULLS Clause.

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 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view department wise employee_id, employee_name, department and the first value of commission, in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,FIRST_VALUE(commission) OVER (PARTITION BY department
                    ORDER BY employee_id DESC
                    ROWS UNBOUNDED PRECEDING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
103 Emp C IT 20
102 Emp B IT 20
107 Emp G Sales
106 Emp F Sales
105 Emp E Sales
101 Emp A Sales
108 Emp H Support
104 Emp D Support

Here we can see that we have fetched first value for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 103’ has 20 commission and hence is fetched against ‘IT’ department.

For ‘Sales’ and ‘Support’ departments the ‘employee_id = 107’ and ‘108’ have NULL as commission, hence NULL is fetched as first value against ‘Sales’ and ‘Support’ departments.

Example 2:

Using FIRST_VALUE Function with PARTITION BY and IGNORE NULLS Clause.

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 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view department wise employee_id, employee_name, department and the first value of commission, in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,FIRST_VALUE(commission IGNORE NULLS) OVER (PARTITION BY department
                    ORDER BY employee_id DESC
                    ROWS UNBOUNDED PRECEDING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
103 Emp C IT 20
102 Emp B IT 20
107 Emp G Sales  
106 Emp F Sales 5
105 Emp E Sales 5
101 Emp A Sales 5
108 Emp H Support  
104 Emp D Support 5

Here we can see that we have fetched first values for the commission column department wise in descending order of ‘employee_id’, for ‘IT’ department the ‘employee_id = 103’ has 20 commission and hence is fetched against ‘IT’ department.

Also observe that for ‘employee_id = 107’ in Sales department the first value encountered is NULL, but the next NON NULL value is 5 and hence 5 is fetched for other employees of ‘Sales’ department.

Similarly for ‘Support’ department, ‘employee_id = 108’ has NULL value for ‘commission’ but the next NON NULL value is 5 and hence 5 is fetched for other employees of ‘Support’ department.

FIRST_VALUE Function without PARTITION BY Clause in Oracle SQL – PLSQL

by

The FIRST_VALUE Function in Oracle SQL / PLSQL is an analytical function and is used to return the first value in an ordered set of values.

  1. If the first value in the ordered set is NULL then, FIRST_VALUE function returns NULL unless we specify IGNORE NULLS.
  2. If we specify IGNORE NULLS then, FIRST_VALUE function returns the first NON NULL value in the ordered list, or NULL if the list contains all the NULL values.
  3. FIRST_VALUE function can be used with and without PARTITION BY clause.

Syntax for using the FIRST_VALUE function in Oracle SQL / PLSQL is:
SELECT column(s)
,FIRST_VALUE(column [IGNORE NULLS]) OVER ([PARTITION BY column] ORDER BY column ROWS UNBOUNDED PRECEDING)
FROM table_name;

Example 1:
Using FIRST_VALUE Function Without IGNORE NULLS Clause.

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 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view the employee_id, employee_name, department and the first value of commission, in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,FIRST_VALUE(commission) OVER (ORDER BY employee_id DESC
                    ROWS UNBOUNDED PRECEDING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
108 Emp H Support
107 Emp G Sales
106 Emp F Sales
105 Emp E Sales
104 Emp D Support
103 Emp C IT
102 Emp B IT
101 Emp A Sales

Here we can see that we have fetched NULL values in the ‘Minimum_Commission’ column because we have sorted the list in descending order of ‘employee_id’, and for ‘employee_id = 108’ the ‘commission’ is NULL , hence NULL becomes the first value that is encountered and is therefore fetched.

Example 2:

Using FIRST_VALUE Function With IGNORE NULLS Clause.

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 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Suppose we wish to view the employee_id, employee_name, department and the first value of commission, in an ordered set, we can achieve the same as:

SELECT employee_id
       ,employee_name
       ,department
       ,FIRST_VALUE(commission IGNORE NULLS) OVER (ORDER BY employee_id DESC
                    ROWS UNBOUNDED PRECEDING) Minimum_Commission
FROM employee;

We will get the following result:

Employee_ID Employee_Name Department Minimum_Commission
108 Emp H Support  
107 Emp G Sales  
106 Emp F Sales 5
105 Emp E Sales 5
104 Emp D Support 5
103 Emp C IT 5
102 Emp B IT 5
101 Emp A Sales 5

Here we can see that we have fetched 5 as the commission value even though the first value encountered was NULL by using IGNORE NULLS clause in FIRST_VALUE function.

ROWIDTONCHAR Function in Oracle SQL – PLSQL

by

The ROWIDTONCHAR function in Oracle SQL / PLSQL is used to cionvert the ROWID to NVARCHAR2 data type, the result is always in National Character Set and 18 characters long.

Syntax for using the ROWIDTONCHAR function Oracle SQL / PLSQL is;
SELECT ROWIDTONCHAR (ROWID)
FROM table_name;

Example:
Using ROWIDTONCHAR 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 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

If we write our query as:

SELECT ROWIDTONCHAR(ROWID)
FROM employee;

We may get the following result:

ROWIDTONCHAR(ROWID)
AAAPD2AABAAAS2aAAA
AAAPD2AABAAAS2aAAB
AAAPD2AABAAAS2aAAC
AAAPD2AABAAAS2aAAD
AAAPD2AABAAAS2aAAE
AAAPD2AABAAAS2aAAF
AAAPD2AABAAAS2aAAG
AAAPD2AABAAAS2aAAH

Here we can see that we have converted all the “rowids” into NVARCHAR2 data type and all the records are 18 characters long.

ROWIDTOCHAR Function in Oracle SQL – PLSQL

by

The ROWIDTOCHAR function in Oracle SQL / PLSQL is used to cionvert the ROWID to VARCHAR2 datatype, the result is always 18 characters long.

Syntax for using the ROWIDTOCHAR function Oracle SQL / PLSQL is;
SELECT ROWIDTOCHAR (ROWID)
FROM table_name;

Example:
Using ROWIDTOCHAR 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 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

If we write our query as:

SELECT ROWIDTOCHAR(ROWID)
FROM employee;

We may get the following result:

ROWIDTOCHAR(ROWID)
AAAPD2AABAAAS2aAAA
AAAPD2AABAAAS2aAAB
AAAPD2AABAAAS2aAAC
AAAPD2AABAAAS2aAAD
AAAPD2AABAAAS2aAAE
AAAPD2AABAAAS2aAAF
AAAPD2AABAAAS2aAAG
AAAPD2AABAAAS2aAAH

Here we can see that we have converted all the “rowids” into varchar2 data type and all the records are 18 characters long.

ROW_NUMBER clause in Oracle SQL – PLSQL

by

The ROW_NUMBER clause in Oracle SQL / PLSQL is basically a windowing clause and is used to assign a unique row number to fetched records based on an ordered list. ROW_NUMBER clause starts numbering from 1.

ROW_NUMBER clause can be used with and without PARTITION BY clause.

Syntax for using the ROW_NUMBER clause without PARTITION BY clause in Oracle SQL / PLSQL is;
SELECT column(s)
ROW_NUMBER () OVER (ORDER BY column)
FROM table_name;

Syntax for using the ROW_NUMBER clause with PARTITION BY clause in Oracle SQL / PLSQL is;
SELECT column(s)
ROW_NUMBER () OVER (PARTITION BY column ORDER BY column)
FROM table_name;

Example 1:
Using ROW_NUMBER () clause without PARTITION BY clause

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 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

Now, suppose we want to see all the records from the employee table and also want to assign a unique row number to each row, then we can achieve the same as:

SELECT ROW_NUMBER () OVER (ORDER BY e.commission) SNo
       ,e.*
FROM employee e;

We will get the following result:

SNo Employee_ID Employee_Name Department Salary Commission
1 104 Emp D Support 30000 5
2 108 Emp H Sales 12000 10
3 105 Emp E Sales 32000 10
4 106 Emp F Sales 20000 10
5 107 Emp G Sales 12000 10
6 101 Emp A Sales 10000 10
7 102 Emp B IT 20000 20
8 103 Emp C IT 28000 20

Here we can see that we have retrieved all the rows from employee table and also assigned a unique row number as ‘SNo’, for convenience.

Example 2:

Using ROW_NUMBER () clause with PARTITION BY clause

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 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

Now, suppose we want to see all the records from the employee table grouped by department and also want to assign a unique row number to each row, then we can achieve the same as:

SELECT ROW_NUMBER () OVER (PARTITION BY e.department ORDER BY e.commission) SNo
       ,e.*
FROM employee e;

We will get the following result:

SNo Employee_ID Employee_Name Department Salary Commission
1 102 Emp B IT 20000 20
2 103 Emp C IT 28000 20
1 101 Emp A Sales 10000 10
2 108 Emp H Sales 12000 10
3 105 Emp E Sales 32000 10
4 106 Emp F Sales 20000 10
5 107 Emp G Sales 12000 10
1 104 Emp D Support 30000 5

Here we can see that we have retrieved all the rows from employee table and also assigned a unique row number to each row department wise i.e. as soon as the department changes the row number starts from 1 till other department is encountered.