Select statement for last and secondlast value

[gerald]

Assume I have a table like:

CustomerName OrderDate OrderPartID
London 7/7/99 33
Paris 7/6/99 22
Rome 7/5/99 22
London 6/3/99 44
Paris 6/4/99 11
Rome 6/20/99 99
London 4/3/99 12
Paris 4/4/99 13
Rome 4/20/99 94

I a looking of a single SQL statement which will find the last and
second last order from customer "London" in a way like

CustomerName LastOrderDate SecondLastOrderDate
London 7/7/99 6/3/99

Can anyone help me?

Gerald

[Simon Hayes]

Something like this might work (but you need to consider what to do if
there are multiple orders on the same date):

select
'London',
max(dt.orderdate) as 'LastOrder',
min(dt.orderdate) as 'SecondLastOrder'
from
(
select top 2 orderdate
from dbo.orders
where customername = 'London'
order by orderdate desc
) dt

Simon