SQL & PL/SQL

Hi All,

I have 3 sets of data in table t1

sno   eff_date  end_date
--------------------------------
1    7/1/2008  6/30/2009
2    7/1/2007  6/30/2008

sno  eff_date   end_date
-----------------------------------
1    5/2/2008     5/2/2008
2    5/1/2008     NULL


sno   eff_date   end_date
------------------------------------
1    7/1/2008    6/30/09 
2    7/1/2007    NULL

Ok here it goes,

I want latest records at last from the table for each set of data. but i want the query to be universal.

1st set of data works fine when when I do

select * from t1 order by end_date ,eff_date;

before we need to go to second set of data I need to explain what null is.
null means active and open coverage.

In the second set i have 5/2/2008-5/2/2008. that means that a customer started his coverage as 5/2/2008- NULL and said that he wants to cancel it and start from 5/1/2008.

so 5/2/2008-NULL has been updated to 5/2/2008-5/2/2008 and we inserted a new record saying 5/1/2008-NULL. so 5/1/2008-NULL is the latest record.

As I said I want latest record in the bottom . I changed my query like this.

select * from t1 order by nvl(end_date, to_date ('12/31/9999', 'mm/dd/yyyy')),eff_date;

This changed query works for both set 1 and set 2.

Now set 3 has a problem.

in set 3 7/1/2008 - 6/30/09 is the latest record. But it is not going to the bottom. as the NVL change I made is putting it on top

Any changes or any Ideas?

Regards,

G.