Three pieces of SQL, which one is best?

• Get the EMP_NO value from the latest period of work
• I think these are all ok, but I'm unsure about the third
• You can assume that there are no duplicate dates
• Old fashioned method SELECT pow.EMP_NO INTO l_emp_no FROM xxdm034_Employee_POW pow WHERE pow.N_I_NUMBER = emp.N_I_NUMBER AND pow.START_DATE = ( SELECT MIN(START_DATE) FROM xxdm034_Employee_POW powd WHERE powd.N_I_NUMBER = pow.N_I_NUMBER AND powd.PAYROLL_TYPE IN ( 'Employee' ) );
• Descending ROWNUM method SELECT powd.EMP_NO INTO l_emp_no FROM ( SELECT pow.EMP_NO FROM xxdm034_Employee_POW pow WHERE pow.N_I_NUMBER = emp.N_I_NUMBER AND pow.PAYROLL_TYPE IN ( 'Employee' ) ORDER BY pow.START_DATE DESCENDING ) powd WHERE ROWNUM = 1;
• Fancy rank method, not sure about this, might have to
• be a sub-select like the ROWNUM version? SELECT pow.EMP_NO , RANK() OVER ( PARTITION BY pow.N_I_NUMBER ORDER BY pow.START_DATE DESCENDING ) rank INTO l_emp_no , l_rank FROM xxdm034_Employee_POW pow WHERE pow.N_I_NUMBER = emp.N_I_NUMBER AND pow.PAYROLL_TYPE IN ( 'Employee' ) AND rank = 1;

Opinions, both on correctness, performance and readability?

Phil Hibbs. Received on Fri Aug 25 2006 - 07:14:11 CDT