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