How do I Query just latest x records of each item in a table? 1

[ljsmith91]

I am not sure if this is possible but wanted to ask the sql experts. I need to pull just the X latest records for each item from a database table. A small sample of the table is below. So, if I just want to pull the 2 most recently dated items/schedules in a query, I am not sure how I would accomplish this. I really need to pull the latest 10 but I wanted to simplify for this question. So, I would need the records with * at the end to get pulled if I wanted latest 2 records of each item/schedule. Can anyone help direct me to how to go about something like this? Thanks in advance.

table_items

item schedule date

a as1 2011-02-10
a as1 2011-04-11 *
a as1 2011-08-12 *
a as2 2011-01-10
a as2 2011-04-10 *
a as2 2011-07-10 *
b bs1 2009-02-10
b bs1 2010-04-11 *
b bs1 2011-08-12 *
b bs2 2008-01-10
b bs2 2009-04-10 *
b bs2 2006-07-10 *

 

[bborissov]

What version of SQL Server you use?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[ljsmith91]

Hi bborissov,

I have to do this same query against 2 different dbs, 1 is MSSQL 2005 and the other Oracle 10.

Any guidance is a great help. Thanks.

 

[bborissov]

Don't know about Oracle, but in SQL Server 2005 and above you could so this:

SELECT item, schedule, date
FROM (SELECT item, schedule, date,
             ROW_NUMBER() OVER (PARTITION BY item, schedule
                                ORDER BY Date DESC) AS RowN
      FROM YourTable) YrTbl
WHERE RowN < 3

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.