How get the result into one row from two rows

[srinivasm]

Hi,

I have requirement, cna somebody give some idea, how to get the solution.

I have the following tables.

1) dtr_locks

Name Null? Type
----------------------------------------- -------- ----------------------------
COURSEID NOT NULL VARCHAR2(50)
COURSETITLE VARCHAR2(100)
USERNAME VARCHAR2(100)
USERTYPE VARCHAR2(20)
USERMAILID VARCHAR2(100)
PUBLISHLOCKSTATUS VARCHAR2(20)
EDITLOCKSTATUS VARCHAR2(20)
PUBLISHLOCKDATE DATE
EDITLOCKDATE DATE

2)dtr_logevent

Name Null? Type
----------------------------------------- -------- ----------------------------
COURSEID VARCHAR2(50)
ACCOUNTID VARCHAR2(50)
COURSETITLE VARCHAR2(100)
COURSEVERSION VARCHAR2(15)
USERNAME VARCHAR2(100)
LOGDATE DATE
ACTIONTYPE VARCHAR2(20)
EVENTSTATUS VARCHAR2(20)




dtr_locks table Desc:

This table just contains other information of the course. Here courseid is primarykey.


dtr_logevent table Desc:

This table contains a course information like courseid,courseversion,logdate,actiontype..
Here one course may contain diff versions. And this table contains actiontype,
there are 4 to 5 action types, they may increase in future. So when ever user changes something then
I insert the actiontype,logdate,courseid,courseversion....


My requirement is that i wanted to get the details of the latest update of the given
courseid,courseversion. if the given courseid,courseversion has the 10 rows, i want a row
with the latest logdate of each actiontype.

I am kind of getting that information, but i want a single row for each courseid,courseversion
irresspective of different action types. If there are two diff action types, the result should one row
with the course deatils and the latest logdate of each action type. But i am getting different rows.

i have tried like this


select log.courseid,log.courseversion,max(to_char(log.logdate,'dd/mm/yyyy MM:SS')),log.actiontype,loc.usertype,loc.publishlockdate
from dtr_logevent log,dtr_locks loc where log.courseid=loc.courseid
and log.courseid in ('ms430','TestCourse')
and log.courseversion in ('ms430_1.0','TestCourse_1.1')
group by log.courseid,log.courseversion,log.actiontype,loc.usertype,loc.publishlockdate;


COURSEID COURSEVERSION
-------------------------------------------------- ---------------
MAX(TO_CHAR(LOG. ACTIONTYPE USERTYPE PUBLISHLO
---------------- -------------------- -------------------- ---------
ms430 -- ms430_1.0--
22/04/2002 04:41-- publishlive-- external-- 22-APR-02

ms430 -- ms430_1.0--
22/04/2002 04:41-- publishpreview-- external-- 22-APR-02


but i want the result something like this

COURSEID COURSEVERSION ACTIONTYPE_p_live ACTIONTYPE_P_preview USERTYPE pUBLISHLOck
--------- --------------- ------------------- ---------------------- --------- -------------
ms430-- ms430_1.0-- 22/04/2002 04:41-- 23/04/2002 02:41-- external-- 22-APR-02



I have tried in so many ways but i didn't get the solution.

thanks for any help.

Srinivas M

[tamilselvan]

Basically you have to use MAX function in the sub query.

Select a.courseid, a.coursetitle , c.logdate, c.actiontype
From DTR_LOCKS A, DTR_LOGEVENT C
WHERE A.COURSEID = C.COURSEID AND
A.COURSETITLE = C.COURSETITLE AND
C.LOGDATE = ( SELECT MAX(B.LOGDATE)
FROM DTR_LOGEVENT B
WHERE A.COURSEID = B.COURSEID AND
A.COURSETITLE = B.COURSETITLE)
;