Hi,
This is a really complicated issue and is hard to explain but i have the following:

select name, MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name

which is fine and brings up the correct results but if I want to find out from those records what another field is in table 2 for each record it pulls up too many results (i want just the one result from table 2 and then find what user it is)

if I do..

select name, table2.username MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name, table2.username

.. it pulls up too many results cos there are different usernames

if i dont group by table2.username then it give an error

Coach me a bit here... If there can be multiple usernames, which one do you want? For a quick and dirty, you could try:-PatP

ok say i have these tables

table 1
ID Computer Name
1 ws1
2 ws2
3 ws3
4 ws4

table 2
ID User Time
2 Bill 22/2/04 15:30
2 Bob 22/2/04 16:40
2 Bill 22/2/04 19:50
2 Jack 22/2/04 13:21
2 Jack 22/2/04 11:22
2 Jack 22/2/04 13:10
3 Jack 22/2/04 19:21
....
Inner join table 2 on table1.id = table2.id

i want to get all the records from table 1 and for each of those records i want to get the maximum Time from table 2. That i can do but I cannot extract the user name for each record because if i include the user colum in the select statement it will pul up a value for each user who was on the one machine.

Sorry i cannot explain it well

I just want to fin out who was the user from the maximum time for each computer

That helps me understand. The solution depends on which SQL engine you are using. I'd try using:-PatP

Yeah that sort of works if i want to search on a users id for the last computer they were on but what if i want to find all the computers where he was the last one logged in?

Maybe I don't understand your question. Can't you just add the user name into the outer WHERE clause to see all of the computers (and times) where that particular user was the last one logged on?

-PatP