Rownum and order by in oracle

ROWNUM or ORDER BY which comes first ?

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.

rownum1

Examples with rownum

rownum2

rownum3

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 :-

orderby1

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

rownum_orderby_wrong.PNG

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

rownum_orderby_correct

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 

Few more tips about rownum

  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.
Advertisements

TO_DATE function 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

image7
SQLDEVELOPER date format

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 people make.

 

image8

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)

image9

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

Also read TO_CHAR Function