Query to provide range of repeat values

I need a query to provide range data where rows have repeated data. I'll explain :

Tables concerned are like this :

Year Cost
---- ----
2000 10
2001 10
2002 20
2003 20
2004 20
2005 15
2006 10

But I need to provide the costs in order of time but with the time as a range like this :

Start End Cost
----- --- ----

2000     2001   10
2002     2004   20
2005     2005   15
2006     2006   10

At first I thought I'd cracked it with :

SELECT DISTINCT a.cost,
(SELECT MIN(year) FROM mytable b WHERE b.cost = a.cost) AS start,
(SELECT MAX(year) FROM mytable c WHERE c.cost = a.cost) AS end
FROM mytable a
ORDER BY start

which would produce what I am looking for if none of the costs were repeated for a later time range (e.g. above where there are two ranges with a cost of 10)

Is this possible ? Received on Thu Feb 19 2004 - 10:03:24 CST