How can I select the first n results of each element in a WHERE .. IN clause

I have a table containing multiple computer names and the date of a connection. Here's an example.

ID    Computer         User         Date         
------------------------------------------------
1      O-1234          User1         2010-07-08        
2      O-1234          User2         2010-08-24           
3      O-1234          User2         2010-09-25
4      O-1555          User3         2010-08-24           
5      O-1555          User3         2010-09-25      

I would like to be able to only select the last 2 or 3 connections for each computer. Also, I can't modify the table. Anybody have 开发者_高级运维an idea ?

Thank you

EDIT: It's a MS Access mdb file

EDIT": Since RANK OVER() can't be used in MS Access, would it be possible to use a temporary table to do the job ?

---

SELECT *
FROM (
    SELECT ID, Computer, User, Date,
    RANK() OVER ( PARTITION BY Computer ORDER BY Date DESC) AS RowNumber
    FROM YourTable)
WHERE RowNumber <= 3

EDIT: Oh... it's Access.

I don't have access to... er... Access, but can try this instead?

SELECT
    logins.ID,
    logins.Computer,
    logins.User,
    logins.Date,
    COUNT(*)
FROM
    YourTable logins INNER JOIN
    YourTable Subsequentlogins ON
        logins.Computer = Subsequentlogins.Computer
        AND logins.Date <= Subsequentlogins.Date
GROUP BY
    logins.ID,
    logins.Computer,
    logins.User,
    logins.Date
HAVING COUNT(*) <= 3

---

You'll need to partition/rank them somehow, which can be pretty database-dependent. If you indicate what database you are using we can give more specific help.

For SQL Server, for example, it would look something like this

SELECT
    Computer
    ,User
    ,Date
    ,LoginRank
FROM
    (SELECT
         Computer
         ,User
         ,Date
         ,RANK() OVER (PARTITION BY Computer ORDER BY Date DESC) as LoginRank
     FROM
         Logins)
WHERE
    LoginRank in (1,2)  -- take the top 2
ORDER BY
    Computer
    ,LoginRank ASC