One Small Problem

[nkakrani]

Dear friends...

Facing one small problem. I have following data in a table

Rec No Cust_id Date
------- -------- -----
1 1001 01/01/2001 10.20.00
2 1001 01/01/2001 10.30.00
3 1001 01/01/2001 09.30.00


I need the record - one less than max date. i.e. Rec No. 1

Please advise what query/PL SQL i should run.

Thank in advance

Nkakrani

[balamv]

select x.recno from <tablename> x where 2 = (select count(*) from <tablename> y where x.<date_column> <= y.<date_column>)

[vanheerj]

I'm sure there's a number of different solutions and this one is most probably not the best

SELECT *
FROM test_sql A
WHERE A.cust_date = (
SELECT MAX(B.cust_date)
FROM test_sql B
WHERE B.cust_date < (
SELECT MAX(C.cust_date)
FROM test_sql C))

[mber]

One clear way is:

select ename, empno, hiredate
from ( select ename, empno, hiredate, rownum r
from ( select ename, empno, hiredate
from emp
order by hiredate DESC )
where rownum <= 2 )
where r = 2