SQL & PL/SQL

Hello Experts

I am having trouble in selecting the record which is one less than the maximum rank  Please see the desired results part.

I am trying using the Rank or Dense_rank but I could not.

Please help in solving this issue.

The Oracle version I am working on is

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Thanks

RB

with t as

(

SELECT 29951 SOURCE_ID,'03/4/2013' CREATE_DATE, 'AAAA' NAME FROM DUAL UNION ALL

SELECT 29951 SOURCE_ID,'03/11/2013' CREATE_DATE, 'BBBB' NAME FROM DUAL UNION ALL

SELECT 29951 SOURCE_ID,'03/21/2013' CREATE_DATE, 'CCCC' NAME FROM DUAL UNION ALL

SELECT 29952 SOURCE_ID,'03/14/2013' CREATE_DATE, 'SSSS' NAME FROM DUAL UNION ALL

SELECT 29952 SOURCE_ID,'03/4/2013' CREATE_DATE, 'TTTT' NAME FROM DUAL UNION ALL

SELECT 29952 SOURCE_ID,'03/24/2013' CREATE_DATE, 'PPPP' NAME FROM DUAL UNION ALL

SELECT 29953 SOURCE_ID,'03/01/2013' CREATE_DATE, 'MMMM' NAME FROM DUAL UNION ALL

SELECT 29953 SOURCE_ID,'03/11/2013' CREATE_DATE, 'RRRR' NAME FROM DUAL UNION ALL

SELECT 29954 SOURCE_ID,'03/24/2013' CREATE_DATE, 'WWWW' NAME FROM DUAL

)

SELECT row_number() over (PARTITION BY source_id ORDER BY SOURCE_ID,create_date  ) rank, source_id,create_date

  FROM t

SOURCE_ID          CREATE_DATE            NAME

29951                    03/11/2013                   BBBB

29952                    03/14/2013                   SSSS

29953                    03/1/2013                     MMMM

29954                    03/24/2013                   WWWW