Urgent help on SQL Query

• SQL_Programmer

  SSC Veteran

  Points: 248

  More actions

  August 1, 2005 at 10:00 am

  #66474

  Can any one help me for the query marked in red?

  ........... LEFT OUTER JOIN (SELECT ItemId,UserId,Max(EffTime) as ETime FROM ItemAuditTrail WHERE NewValue ='Matched'  GROUP BY ItemId,UserId  ) AS T3  ON T3.ItemId = Item.Id

  15058509	423	Name0	01:40.9
  15058509	425	Name1	01:47.9
  15058509	447	Name2	59:38.4

  query gives me the output as mentioned above, means it does not give me single record if there are more then one users. I want only one row with last effective time.

  Output should be

  15058509

  425

  Name1

  01:47.9

  Thanks.

   

• mxhxr

  SSChasing Mays

  Points: 611

  More actions

  August 1, 2005 at 10:26 am

  #578610

  You could possibly insert the record set into a temp table, and query out of the temp table:

  SELECT MAX(EffTime) from #tempTable

  There is probably a more elegant solution to this, but this may work.

• SQL_Programmer

  SSC Veteran

  Points: 248

  More actions

  August 1, 2005 at 3:47 pm

  #578690

  Ok i tried your idea but this still gives all rows as i want to return userid too

• Mike Howell

  Mr or Mrs. 500

  Points: 521

  More actions

  August 2, 2005 at 12:19 am

  #578729

  How about

   

  SELECT TOP 1 ItemId,UserId,Max(EffTime) as ETime FROM ItemAuditTrail WHERE NewValue ='Matched'  GROUP BY ItemId,UserId  ORDER BY EffTime DESC

   

  This will give you the first record with the largest time value.