Filtering results

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 errorIt is not clear what do you want - what about this? May be it will help you describe your problem.

select name, max(table2.username),MAX(table2.time) from table1 INNER JOIN table2 on table1.id = table2.id GROUP BY name|||ok say i have these tables

table 1
ID 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|||Originally posted by steve1981
ok say i have these tables

table 1
ID 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

Let say for row with id=2 (table 1) you have four users - which one do you want to see in results?|||i want to get all id = 2 and the max login time of those so it would be

2 Bill 22/2/04 19:50

and i want to pull out the username which in this case would be bill|||create table #table1(id int,namen varchar(10))
create table #table2(id int,usern varchar(10),timen datetime)
go
insert #table1 values(1,'ws1')
insert #table1 values(2,'ws2')
insert #table1 values(3,'ws3')
insert #table1 values(4,'ws4')

insert #table2 values(2,'bill','2004-04-20 15:30:00.000')
insert #table2 values(2,'bob','2004-04-20 16:40:00.000')
insert #table2 values(2,'bill','2004-04-20 19:50:00.000')
insert #table2 values(2,'Jack','2004-04-20 13:21:00.000')
insert #table2 values(2,'Jack','2004-04-20 11:22:00.000')
insert #table2 values(2,'Jack','2004-04-20 13:10:00.000')
insert #table2 values(3,'Jack','2004-04-20 19:21:00.000')

select q.id,q.mt,(select top 1 usern from #table2 where id=q.id and timen=q.mt)
from
(select t1.id,max(timen) mt
from #table1 t1
join #table2 t2 on t2.id=t1.id
group by t1.id) as q