Oracle Tips

Date Functions

Get first day of month : ADD_MONTHS( LAST_DAY(sysdate),-1)+1
Get last day of month : LAST_DAY(sysdate)
Get first day of week : TRUNC(sysdate,’W’)
Get Last day of week : TRUNC(sysdate,’W’)+6

Get n highest values from SQL only.

I was recently posed a question by a colleague :

How do you get the highest 5 values only from a table in SQL. In pl/sql I would have declared a cursor ordered it by the values in descending order and read back 5 rows. At first glimpse this seemed easy enough, I thought I could use rownum to filter out the rows, but of course rownums are only assigned to retrieved rows so I couldn’t use that. After thinking about the problem for a while I came up with the following SQL :

select a.numcol
from mytable a
where 5 >
select count(*)
from mytable b
where b.numcol > a.numcol
order by a.numcol desc