# 5 different ways to find TOP N queries

5 different ways to find TOP N queries

Let us write five different queries to list the top 4 highest paid employees from emp table.

1) Using ROWNUM
2) Using ROW_NUMBER()
3) Using RANK()
4) Using DENSE_RANK()
5) Using MINUS

1) Using ROWNUM

select * from (select empno,salary,rownum as rk from emp order by salary desc) where rk < 5;

2) Using ROW_NUMBER()

select * from (select empno,salary,row_number() over(order by salary desc) as rk from emp) where rk < 5;

3) Using RANK()

select * from (select empno,salary,rank() over(order by salary desc) as rk from emp) where rk < 5;

4) Using DENSE_RANK()

select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk < 5;

Note that dense_rank will return multiple records if there is a tie.

5) Using MINUS

select empno,salary from emp
minus
select empno,salary from (select empno,salary,rownum as rk from emp order by salary desc) where rk > 4;

You can also read – Nth highest salary

# Rownum and order by in oracle

ROWNUM

ROWNUM is a psuedocolumn to return the row number of the row in a table. The order will be based on how oracle selects a row from table.

Examples with rownum

Note that rownum will be decided by Oracle itself ( Oracle will assign a number based on how it retrieves from storage/memory)

Order by

Order by is a sql clause which used to order a selected column

Example :-

Order by and Rownum together

Why we should use rownum and Order by clause together ? Many experienced developers ( Mainly java developers ) think they can write rownum and order by in same where clause to get the top N query.

For example – To find lowest salary employee developers write like below

select * from emp where rownum = 1 order by salary asc ; —  wrong query

see the result

Result is wrong.  It should be salary 7500 ( Empno – 14 )

Why ? Oracle applies the ROWNUM first and then applies the order by clause.

See the correct query below

So always apply the order by and in next level apply the rownum.

To find a lowest salary employee :-

select * from emp where rownum = 1 order by salary asc ; —  wrong query

select * from (select * from emp order by salary asc) where rownum = 1 — correct query

1. Do not write ROWNUM = 0 ( There is no record having ROWNUM = 0)
2. Do not write ROWNUM <= 1 instead write ROWNUM = 1
3. In general Oracle assigns ROWNUM while retrieving the records, it is not based on Primary key column
4. Do not write ROWNUM > 1, which will no yield any results
5. ROWNUM and ROW_NUMBER() is not same.

# ROW_NUMBER oracle

ROW_NUMBER is an example of analytical function. It returns a unique number for each record.

Consider below emp table data

 EMPNO EMPNAME SALARY DEPTNO 10 Bill 12000 5 11 Solomon 10000 5 12 Susan 10000 5 13 Wendy 9000 1 14 Benjamin 7500 1 15 Tom 7600 1 16 Henry 8500 2 17 Robert 9500 2 18 Paul 7700 2

ROW_NUMBER()

Select empno, deptno, salary, row_number() over(order by salary) from emp;

ROW_NUMBER assigns a unique number by ordering by salary ascending by default

You can use row_number to find top N records

Below query returns first 3rd to 5th records

select * from
(
select empno, deptno, salary, row_number() over(order by salary) as rn from emp
) where rn between 3 and 5
;

ROW_NUMBER() with partition by clause

select empno, deptno, salary,
row_number() over(partition by deptno order by salary)
from emp;

Returns unique number within the window group, ie deptno. Each department it will find the employees based on their salary.

ROW_NUMBER() with NULLS LAST

This ignores the records having salary as null and give least priority for ranking

Select empno, deptno, salary,
row_number() over(order by salary nulls last)
from emp;

# Difference between rank and dense_rank Oracle

What is the difference between RANK and DENSE_RANK functions in Oracle ?

In simple words both does the same except RANK skips if there is a tie and DENSE_RANK does not. So remember to use DENSE_RANK to use in Nth largest queries.

Detailed explanation of RANK and DESNSE_RANK given in another posts

RANK Function

DENSE_RANK Function

Example :-

Consider below emp table data

 EMPNO EMPNAME SALARY DEPTNO 10 Bill 12000 5 11 Solomon 10000 5 12 Susan 10000 5 13 Wendy 9000 1 14 Benjamin 7500 1 15 Tom 7600 1 16 Henry 8500 2 17 Robert 9500 2 18 Paul 7700 2

select empno,salary,
rank() over(order by salary asc) “rank”,
dense_rank() over(order by salary asc) “dense_rank”
from emp;

See the result

RANK function skips number 8 while DENSE_RANK does not.

# sys_context Function Oracle and its usage

SYS_CONTEXT function returns set of defined values within the database(based out of parameters). This can be called from both SQL and PLSQL.

Example:-

SELECT SYS_CONTEXT (‘USERENV’, ‘NLS_DATE_FORMAT’)  FROM DUAL;

return NLS_DATE_FORMAT as DD-MON-RR ( In my session ). USERENV is the parameter

Another example below

Below few list of all SYS_CONTEXT parameters (most important )

 Parameter Description CURRENT_SQL First 4000 bytes of the current SQL. CURRENT_SQL_LENGTH Current SQL Length DB_DOMAIN DB Domain DB_NAME DB Name HOST Host Name INSTANCE Instance ID INSTANCE_NAME Instance Name IP_ADDRESS IP Address ISDBA Yes if logged as DBA LANG Language Country LANGUAGE Language Detail MODULE Tool Name NETWORK_PROTOCOL Eg:- TCP NLS_CALENDAR Eg:- Gregorian NLS_CURRENCY Currency NLS_DATE_FORMAT Date format NLS_DATE_LANGUAGE Date Language NLS_SORT Eg:- Binary NLS_TERRITORY NLS country OS_USER OS User Name SERVER_HOST Server Host SERVICE_NAME Service Name SESSION_USER Session User Name SESSION_USERID Session User ID SESSIONID Session Id SID Process ID TERMINAL Client Machine Name

Usage

If I want to create audit table with information like userid, machine name, ip address, SYS_CONTEXT will help. See below sample

Create table emp_audit
(
emp_id number,
create_date timestamp default systimestamp,
created_by varchar2(50) default sys_context (‘userenv’, ‘session_user’),
module varchar2(50) default sys_context (‘userenv’,’MODULE’)
);

insert into emp_audit(emp_id) values (1000);

select * from emp_audit;

You can see SYS_CONTEXT used in another post Simple Debugging Tool

# TO_DATE Oracle

TO_DATE function in Oracle is the most useful as well as ‘annoying’ function.

TO_DATE function converts string into date data types.

Eg :-

select to_date(‘2016/11/13′,’yyyy/mm/dd’) from dual;

returns  2016/11/13

Note that Oracle converts string ‘2016/11/13’into Date ‘2016/11/13’. Now this value can be inserted into a table having a column date data type.

Note :- Before doing any testing on date columns make sure the default date format settings

For example in my sqldeveloper tool, go to Tools–> Preferences –> Database –> NLS

Below sql should have returned 2016/11/13, but shows 13-NOV-16 which is in sqldeveloper nls date format. This is the most common mistake developers make.

Alternatively you can use below sql also to change the date format.

alter session set nls_date_format = ‘dd-mon-yyyy’;

The above SQL can be used from SQLPLUS also.

If you do not want to change the nls settings, you can use TO_CHAR function also in required format, see below example.

select to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’yyyy/mm/dd’) from dual;

(TO_CHAR and TO_DATE)

Important :-  Oracle stores date data type with time.

Below are the most used formats with TO_DATE

 Format Description Example Value AD AD or BC to_char(to_date(‘2016/11/13 AD’,’yyyy/mm/dd AD’),’AD’) AD BC AD or BC to_char(to_date(‘2016/11/13 BC’,’yyyy/mm/dd AD’),’AD’) BC D Day of week (1 7) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’D’) 1 DAY Name of day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DAY’) SUNDAY DD Day of month (1 31) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DD’) 13 DDD Day of year (1 366) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DDD’) 318 DY Abbr name of day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DY’) SUN HH Hour of day (1 12) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’HH’) ‘01’ HH12 Hour of day (1 12) to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH12′) ‘04’ HH24 Hour of day (0 23) to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH24′) 16 IW Week of year to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’IW’) 45 J Julian day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’J’) 2457706 MI Minute (0 59) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MI’) 12 MM Month (01 etc) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MM’) 11 MON Abbr month to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MON’) NOV MONTH Name of month to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MONTH’) NOVEMBER AM AM or PM to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) AM PM AM or PM to_char(to_date(‘2016/11/13 01:12:35 PM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) PM Q Quarter of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’Q’) 4 RRRR Round year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’RRRR’) 2016 SS Second (0 59) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’SS’) 35 WW Week of year (1 53) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’WW’) 46 W Week of month (1 5) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’W’) 2 YYYY Last 4 digit(s) of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YYYY’) 2016 YY Last 2 digit(s) of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YY’) ‘16’

Always check below before you start using TO_DATE function

1. NLS_DATE_FORMAT ( SELECT sys_context (‘userenv’,’NLS_DATE_FORMAT’) FROM DUAL )

or

2. use TO_CHAR for formating

# 10 must-know Oracle Interview questions

Interviewing a candidate for a particular job is a tedious task, there are many factors which effect your selection. Managers has to decide within short time span with minimum questions.So if we know the most possible or probable questions before interview we can be prepared. Here 10 must-know Interview questions ( there is no ranking )

1. What is the difference between Procedure and Function ?

1) Function must return a value and procedure need not.

2) Function can be used in SQL with some restrictions. Procedure cannot be called directly from SQL.

2. What is the difference between DELETE and TRUNCATE ?

1) DELETE is a DML command and TRUNCATE is a DDL command.

2) TRUNCATE re-set the memory blocks after execution and much faster than DELETE in most of the circumstances.

3. What is the difference between PRIMARY KEY and UNIQUE KEY constraints ?

1) UNIQUE KEY columns can have null values but PRIMARY KEY column cannot accept null values.

2) A table can have only one PRIMARY KEY column (composite primary key is ok) but many UNIQUE KEY columns allowed.

4. What is Cartesian Product ?

1) If two or more tables are joining without join condition will result into Cartesian products.

2) If table A has 2 rows and table B has 4 rows then Cartesian product between A and B will return 8 rows ( 2 multiply by 4 )

5. What is a Transaction ?

1) Transaction is a logical unit of work which will end in a consistent status

2) In Oracle we can control transactions using Save points, Commit, rollback etc

6. What is a Package, why do we go for packages ?

1) Package is a collection of one more program units having Procedure, Function etc

2) Depends on business logic, all related program units can be tied into a single package for better maintenance, readability etc

7. What is triggers, where we use triggers ?

1) Procedural code which automatically executes based on an event like insert,update, delete etc

2) Auditing, Logging etc

8. How to do sql performance tuning ?

1) Basic sql performance starts with EXPLAIN PLAN

2) Explain cost, index, access methods, plan etc

9. What is the most challenging/difficult feature in Oracle ?

1) This is the most tricky question, answer based on your experience.

2) Do not answer with a trivial problem, try something advanced feature

10. What is a materialized view ?