Microsoft SQL Server forum - Select statement for last and secondlast value

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

Select statement for last and secondlast value

gerald

2005-08-24, 11:24 am

Assume I have a table like:

CustomerName OrderDa
te 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 LastOrd
erDate SecondLastOrd
erDate
London 7/7/99 6/3/99

Can anyone help me?

Gerald

Simon Hayes

2005-08-24, 11:24 am

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

Sponsored Links