Re: Help in Query Latest -- Urgent

Hi Shafeek Khalidh, thanks for writing this:
> Hi all,
>
> Here is a table structure,
> Cource_table
> ============
> ID
> End_Cource_date
> Teacher_id
>
> Test Data...........
> ID --End_Cource_date-----Teacher_id
> ====================================
> 1 ---1/1/2003------------T100
> 2----2/2/2003------------T100
> 3----1/2/2003------------T100
> 4----Null-----------------T100
> 5----2/1/2003------------T101
> 6----3/1/2003------------T101
> 7----4/1/2003------------T101
> 8----2/1/2003------------T101
>
> I want to get the latest two ids corresponding to each teacher.If the date
> is null, Thats the latest date. (mm/dd/yyyy format)
>
> One more thing, I need the results as the following format,
> Teacher_id------End_Cource_date-----Second_End_Cource_date
> ==============================================
> T100------------Null-------------------2/2/2003
> T101------------4/1/2003--------------3/1/2003
>
>
> Please help
> Thanks
> Shafeek Khalidh
>

Let's see. The SELECT statement gets all rows. The GROUP BY teacher_id clause gets distinct teachers. The "max()" function gets the latest date. To make a NULL be the latest date, you use something like "nvl (end_cource_date,sysdate+9999)". Put them all together correctly, with the appropriate date functions and column aliases, and you'll get your answer.

-- 
[:%s/Karsten Farrell/Oracle DBA/g]