ROWNUM VS ORDER BY

As everybody knows if I need to sort out the result of a SELECT I have to include at the end of  query the 'ORDER BY' keyword.

Following an example : 

select NUMERI_GRAD from grad where sect_id = 101 and grade_type= 'FI' order by NUMERI_GRAD desc;

Output : 99,92,91,etc.

However if I need to limit the result of the previous query , how can I get the right result ?

We can include the ROWNUM keyword, can't we ?

Yes, but pay special attention to the result :

select NUMERI_GRAD from grad where sect_id = 101 and grade_type = 'FI' and rownum <4  order by NUMERI_GRAD desc;

Output :  99,88,77,etc.

We have a problem. The  "NUMERI_GRAD" code isn't well ordered. This is why the 'ROWNUM'  keyword is applied before the 'ORDER BY' .

However a handy option to solve the problem could be the following :

select NUMERI_GRAD from (select NUMERI_GRAD from grad where sect_id = 101 and grade_type= 'FI' order by NUMERI_GRAD desc) where rownum < 4;

This query looks like an inline-view, doesn't it ?

See you in my next blog.

Byeeee