how do I get the 2 most recent records

Question

I have 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

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

Based on the version, you can use different methods http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx