This week is very busy time for me. Because I have to implement HL7 segment with Oracle Stored Procedure. As we all know, Oracle SQL command isn't that same with Microsoft SQL. That's why some of command are very complicated for me to implement. Today, I need to retrieve the only one latest record from table with Oracle. In Coldfusion, it's very easy. To put maxrows = "1" and descending in query as follow:

view plain print about
1<cfquery name="qryGetLatest" datasource="myDSN" maxrows="1">
2    SELECT U_ID FROM TBL_USER
3    ORDER BY U_ID DESC
4</cfquery>

In Microsoft, it's very simple. To put TOP command and descending in query as follow:

view plain print about
1SELECT TOP U_ID FROM TBL_USER
2ORDER BY U_ID DESC

In Oracle, it's not that easy. If we put ROWNUM and descending in query, the first record will be returned. Because ROWNUM doesn't work with Order By descending in query. That's why we need to do with query with query formula in Oracle as follow:

view plain print about
1SELECT U_ID FROM
2    (SELECT U_ID FROM TBL_USER
3    ORDER BY U_ID DESC) CLONE_USER
4WHERE ROWNUM = 1

Phweee...!!! Oracle crack my brain again. :)

This entry was posted on February 22, 2011 at 6:11 AM and has received 1160 views. There are currently 0 comments. Print this entry.