Returning most recent 5 in a group

[AlanKW]

I have something similiar to thread701-871569 where I have a table storing the login times to the companies Access databases. I have the UserName, the computername, Timestamp, Database name & autonumber primary key.

The purpose is to get a sense of who is logging on where. I am trying to find the most recent 5 UserNames per machine. If I was trying to get the single most recent, it'd be easy - however I am at a loss to get the most recent 5.

Any thoughts?

 

[amorous]

Here you go buddy...try something like this...also try the other variations on the same lines

SELECT t1.*
FROM [MyTable] t1
WHERE t1.TimeStamp In (select top 5 t2.TimeStamp from [MyTable] t2 where t2.ComputerName = t1.ComputerName ORDER BY t2.TimeStamp Desc);

-VJ

 

[AlanKW]

Apolgies for a newbie question...

By T1, T2 am I using the same root table twice?

ie:
T1=tbl_MachineUseage
T2=tbl_MachineUseage_1

I think Enigma007 figured it out, but I dont have enough coffee available..

 

[amorous]

Yes...both T1 and T2 refer to the same table...

T1 and T2 are just used as alias names

-VJ

 

[AlanKW]

Again Apologies (read some of my other posts, I'm not a complete newbie!)

If we're using the same table twice, wouldnt the FROM Line contain both tables?

I've seperated out the IN line to try and get a grasp of it and I get 7 records of the same (most recent) timestamp I know I didn't need to do the AS T1/AS T2, but I'm taking baby steps at theis point.

[tt]SELECT TOP 5 T2.Timestamp
FROM tbl_MachineUseage AS T2, tbl_MachineUseage as T1
WHERE (((T2.MachineName)=[t1].[MachineName]))
ORDER BY T2.Timestamp DESC; [/tt]

 

[amorous]

you said that you wanted most recent 5 UserNames per machine, but the query who wrote will result in 5 most recent rows from the whole table AND NOT 5 MOST RECENT PER MACHINE

-VJ

 

[AlanKW]

yes - but since I was having trouble with the query in your initial post, I took the IN (select...) into its own query for temporary purposes to get the proper syntax, etc.. before trying the main query again. But when I noticed that the query shown in the in(select) in my last post was returning the same record 7 times, I realized that I was probably going down the wrong path.