how do I get the 2 most recent records

I have 开发者_JS百科a table similar to the example shown below. I would like to be able to select the two most recent entrys for each accountNo. I am using Microsoft SQL 2000. Thank you for any help that you can provide.

AccountNo,  DateOfOrder,    OrderID
-----------------------------------------
 123,        March 1 2010,     1
 222,        March 3 2010,     2
 123,        April 1 2010,     3
 345,        March 15 2010,   77
 123,        june 1 2010,     55
 123,        march 5 2010,    33
 345,        march 1 2010,    99
 222,        june 1 2010,     7
 222,        june 2 2010,  22

For SQL 2000 this would work

SELECT  a1.AccountNo,  a1.DateOfOrder,  a1.OrderID
FROM Accounts a1
LEFT OUTER JOIN Accounts a2
  ON a2.AccountNo = a1.AccountNo AND a1.DateOfOrder < a2.DateOfOrder
GROUP BY a1.AccountNo,  a1.DateOfOrder,  a1.OrderID
HAVING COUNT(*) < 2

select the most recent set, and select the set that has the maximum date that is less than the most recent set.

If the field DateOfOrder is what you want to sort by, then SELECT TOP 2 * from table .. ORDER BY DateOfOrder

If not, I think the best solution would be to add an InsertDate field to the table.

You can have a trigger auto updating that field upon insert with current datetime.

Then just SELECT TOP 2 * from table .. ORDER BY InsertDate

how about something like:

select * from Account a
where OrderID in
  (select top 2 OrderID from Account
   where AccountNo = a.AccountNo
   order by DateOfOrder desc)