Problem with Grouping and max()

• troyrowlands

  SSC Enthusiast

  Points: 116

  More actions

  September 25, 2012 at 2:31 am

  #267236

  Basically the data is access data to a building. I want to establish if the person is in or out of the building. There are two access points one for IN and one for OUT. the SQL I have so far is as follows which is the select from a view called AD_in_OUT LOG

  SELECT Username, thumbnailPhoto, [Display Name], Department, USERID, email, BADGENUMBER, NAME, CHECKTYPE, CHECKTIME

  FROM dbo.[AD_in_Out LOG]

  So I need to group by USERID and get the latest time for each USERID from CHECKTIME. I then need to establish if the latest time was an OUT or IN from the CHECKTYPE column. It seems simple, but I am not getting the required results. What is the best way to do this as the view looks up from 3 different tables as well so server overhead needs to be minimal.

  thanks

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  September 25, 2012 at 2:43 am

  #1541702

  Try using ROW_NUMBER instead

  WITH CTE AS (

  SELECT Username, thumbnailPhoto, [Display Name], Department, USERID, email, BADGENUMBER, NAME, CHECKTYPE, CHECKTIME,

  ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY CHECKTIME DESC) AS rn

  FROM dbo.[AD_in_Out LOG])

  SELECT Username, thumbnailPhoto, [Display Name], Department, USERID, email, BADGENUMBER, NAME, CHECKTYPE, CHECKTIME

  FROM CTE

  WHERE rn=1;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• terry999

  SSCertifiable

  Points: 5032

  More actions

  September 25, 2012 at 3:38 pm

  #1542112

  SELECT * FROM [AD_in_Out LOG] as tLog

  INNER JOIN

  (SELECT USERID, MAX(CHECKTIME) as LastTime

  GROUP BY [userid]) as tREs

  ON tLog.Userid = tRes.Userid

  AND tLog.CHECKTIME = tREs.LastTime

• troyrowlands

  SSC Enthusiast

  Points: 116

  More actions

  September 27, 2012 at 6:32 am

  #1542836

  Thank You, this seemed to work as required.