CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Ehsan
Ehsan

Reputation: 33

in my query When RowNum =1 does not get null value

In my query 

Select AT.PRE_LAST_DATE 
From AUTHORIZATION AT 
Where AT.PROJECT_ID = 5 and AT.PRE_DESIG_ID = 48   
Order By AT.ID Desc;

I am getting two dates

  1. NULL
  2. 17-May-19 00.00.00

But when I use Rownum = 1, it does not return null value 

Select AT.PRE_LAST_DATE 
From AUTHORIZATION AT 
Where AT.PROJECT_ID = 5 and AT.PRE_DESIG_ID = 48 and ROWNUM = 1  
Order By AT.ID Desc;

It returns:

  1. 17-May-19 00.00.00

Now how do I get the first null value?

Upvotes: 2

Views: 590

Answers (1)

iakobski
iakobski

Reputation: 1274

The ORDER BY is applied after the WHERE clause, which returns only one of the rows in undefined order. To order first, then select the top row, use a subquery:

Select PRE_LAST_DATE 
From 
    (Select AT.PRE_LAST_DATE From 
        AUTHORIZATION AT 
        Where AT.PROJECT_ID = 5 and AT.PRE_DESIG_ID = 48
        Order By AT.ID Desc)
Where ROWNUM = 1;

Upvotes: 2

Related Questions