Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: 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 error
    Last edited by steve1981; 04-20-04 at 10:13.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    It 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

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    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

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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?

  5. #5
    Join Date
    Apr 2004
    Posts
    6
    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

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •