CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
fishhead
fishhead

Reputation: 6109

how do I get the 2 most recent records

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

Upvotes: 0

Views: 9063

Answers (4)

krock
krock

Reputation: 29629

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)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453910

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

Upvotes: 3

Amirshk
Amirshk

Reputation: 8258

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

Upvotes: 0

Randy
Randy

Reputation: 16673

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

Upvotes: 1

Related Questions