Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Grouping and max() Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 2:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 6:23 AM
Points: 8, Visits: 26

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
Post #1363853
Posted Tuesday, September 25, 2012 2:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 1,817, Visits: 21,299

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



Post #1363857
Posted Tuesday, September 25, 2012 3:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 12, 2015 2:07 AM
Points: 133, Visits: 537
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

Post #1364322
Posted Thursday, September 27, 2012 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 27, 2012 6:23 AM
Points: 8, Visits: 26
Thank You, this seemed to work as required.
Post #1365196
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse