DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to retrieve three coloumns from a table!!!!!

  1. #1
    Join Date
    Mar 2002
    Posts
    6

    Question

    Hi friends,

    I have a table with 6 coloumns named:

    courseId(varchar)
    userid(varchar)
    type(varchar) --can be update,preview or live
    update(date)
    preview(date)
    live(date)

    the courseId is repeated many times and every entry will have only one of the coloumns which has dates and the other two date coloumns will be empty.

    Now I want to retrieve the userid,type and the date coloumn based on the type (for ex if the type is update I want to get the greatest of update coloumn) where type= update and update = greatest(update).


    I want the latest two live dates and its complete rows and one latest preview dates and its complete row.

    So totally i wnat to retrieve 3 rows based on the courseid and type.

    it would be very great ful if somebody help me out to come up giving a rope.

    thanx
    satish
    regards,
    Satish Kumar K

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Try this:

    select * from TBL
    where type='UPDATE' and
    ubdate = (select max(update) from tbl)
    union
    select * from TBL
    where type='PREVIEW' and
    preview = (select max(preview) from tbl)
    union
    select * from TBL
    where type='LIVE' and
    live = (select max(live) from tbl)
    ;


  3. #3
    Join Date
    Mar 2002
    Posts
    6
    thanx a lot Shestakov

    A slight addition to the requirement.
    I not only want the latest date but also the second latest date.

    can u please help on that.

    for getting the latest date we can use max(publishdate) but what should I do for getting the second latest date.


    thanx
    satish kumar K.
    regards,
    Satish Kumar K

  4. #4
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    query

    HI,
    Can u please check the result is the your desired result?

    PAL_TEST
    C_ID VARCHAR2 (5),
    U_ID VARCHAR2 (5),
    T_ID VARCHAR2 (10),
    U_DATE DATE,
    P_DATE DATE,
    L_DATE DATE


    Query
    -------

    ( latest two l_id & latest one l_id )


    12:44:58 Palani>>select * from pal_test;

    C_ID U_ID T_ID U_DATE P_DATE L_DATE
    ----- ----- ---------- --------- --------- ---------
    c_1 u_1 UPDATE 10-JAN-02
    c_2 u_2 Preview 11-JAN-02
    c_3 u_3 Live 13-JAN-02
    c_4 u_4 UPDATE 17-JAN-02
    c_5 u_5 Preview 15-JAN-02
    c_6 u_6 Live 16-JAN-02
    c_7 u_7 UPDATE 14-JAN-02
    c_8 u_8 Preview 18-JAN-02
    c_9 u_9 Live 19-JAN-02



    --

    12:40:46 Palani>>ed
    Wrote file afiedt.buf

    1 select * from ( select * from pal_test where t_id = 'Preview' order by p_date desc)
    2 where rownum < 3
    3 union
    4 select * from ( select * from pal_test where t_id = 'Live' order by l_date desc)
    5* where rownum < 2
    12:44:57 6 /

    C_ID U_ID T_ID U_DATE P_DATE L_DATE
    ----- ----- ---------- --------- --------- ---------
    c_5 u_5 Preview 15-JAN-02
    c_8 u_8 Preview 18-JAN-02
    c_9 u_9 Live 19-JAN-02



    Regards,
    RP Kumar
    You Can Win, if u believe Yourself

  5. #5
    Join Date
    Mar 2002
    Posts
    6
    hi RP,

    yeah exactly needed the same which u have mentioned.

    thanx a lot for the great help.

    Thanx a lot both RP and Shestakov

    satish Kumar K
    regards,
    Satish Kumar K

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