latest date problem

[pushaskhelp]

have one table with columns: ID and submission_date
now I have to select ID from this table where submission_date is latest to given date.

[debasisdas]

What exactly you mean by submission_date is latest to given date ?

[pushaskhelp]

I will explain with example:

suppose there is
ID submission_date (yy.mm.dd)
1 2009-07-06
2 2009-03-07
3 2009-06-06
4 2010-06-06
5 2009-07-06

now if I say SELECT submission_date latest to '2009-06-06' then I want only ID 1 and 5 which is latest(close) to given date( bcz ID 1 and 5 have same submission_date )
_______________ _______________ ______

[OraMaster]

I will explain with example:

suppose there is
ID submission_date (yy.mm.dd)
1 2009-07-06
2 2009-03-07
3 2009-06-06
4 2010-06-06
5 2009-07-06

now if I say SELECT submission_date latest to '2009-06-06' then I want only ID 1 and 5 which is latest(close) to given date( bcz ID 1 and 5 have same submission_date )
_______________ _______________ ______

Try it out

SELECT ID,submission_date
  FROM <table name>
 WHERE submission_date =
          (SELECT submission_date
             FROM (SELECT DISTINCT submission_date,
                                   DENSE_RANK () OVER (ORDER BY submission_date) seq_num
                              FROM <table name>
                             WHERE submission_date >
                                          TO_DATE ('<input date>', 'YYYY-MM-DD'))
            WHERE seq_num = 1)