I am trying to do insert to a table and it uses one select statement for one column. Below is the illustration of my query.
INSERT INTO MY_TBL (MY_COL1, MY_COL2)
(SELECT DATA FROM FIR_TABL WHERE ID = 1 AND ROWNUM = 1 ORDER BY CREATED_ON DESC),
ORA-00907 Missing right Parenthesis
Both the current answers ignore the fact that using
order by and
rownum in the same query is inherently dangerous. There is absolutely no guarantee that you will get the data you want. If you want the first row from an ordered query you must use a sub-query:
insert into my_tbl ( col1, col2 ) select data, 'more data' from ( select data from fir_tabl where id = 1 order by created_on desc ) where rownum = 1 ;
You can also use a function like
rank to order the data in the method you want, though if you had two
created_on dates that were identical you would end up with 2 values with
rnk = 1.
insert into my_tbl ( col1, col2 ) select data, 'more data' from ( select data , rank() over ( order by created_on desc ) as rnk from fir_tabl where id = 1) where rnk = 1 ;