SQL help - top two for each, with numeric value 3

[mp9]

Sorry if this is a dumb question.

I need to run a query that returns the two records with the earliest date for each reference number. Also, I need to return a numeric value of 1 or 2 to indicate the position.

For example, if my data was:

REF DATE
111 1/1/2005
111 2/2/2005
111 3/3/2005
222 7/7/2006
222 8/8/2006
222 9/9/2006
333 4/4/2007

Then the query should return:

REF DATE ORDINAL
111 1/1/2005 1
111 2/2/2005 2
222 7/7/2006 1
222 8/8/2006 2
333 4/4/2007 1

I should add that this has to be achievable in plain SQL, as I am using the query as the source for a Crystal Reports Command statement.

Thanks in advance.

 

[Dagon]

If you have Oracle enterprise, this is fairly easily achievable using ranking functions. The syntax would look something like:

select * from
(select ref, date, rank() over (partition by ref order by date) as rnk
from table)
where rnk <= 2

 

[mp9]

If I could give more than one five-pointed pink recognition-type symbol, I would. Many, many thanks.

 

[KenCunningham]

Pity you can't mp9, let me do it for you!

I don't mind people who aren't what they seem. I just wish they'd make their mind up.