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

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

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)
;

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.

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,

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