How get the result into one row from two rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How get the result into one row from two rows

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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)
    ;



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width