Last date per userid 1

[Cpreston]

HI

I am trying to get the last date by a userid they last logged something.

If I sue the following simple command I get the last date in the table, which is correct.

SELECT MAX(dbo.ScanTrackImage.DateTimeTaken) AS MaxTaken
FROM dbo.ScanTrackImage

However the table as various user id, so what I want it the last time each user id last logged something per the maxtaken date. I ahve the following query but I am getting all the rows not just the maxdate per user. I am obviously doing something incorrect somewhere , I ahve googled it but keep coming back to this attemp. Any ideas please, thanks in advance.

SELECT      MAX(dbo.ScanTrackImage.DateTimeTaken) AS MaxTaken, dbo.ScanTrackImage.DateTimeScanned, dbo.ScanTrackImage.ScannedByID, dbo.ScanTrackImage.ExternalID 
              --dbo.Users.Name
FROM            dbo.ScanTrackImage INNER JOIN
                         dbo.Users ON dbo.ScanTrackImage.ScannedByID = dbo.Users.UserID
GROUP BY  dbo.ScanTrackImage.DateTimeTaken, dbo.ScanTrackImage.DateTimeScanned, dbo.ScanTrackImage.ScannedByID, dbo.ScanTrackImage.ExternalID, 
                         dbo.Users.Name
HAVING        (dbo.ScanTrackImage.ScannedByID IN (290, 299, 301, 310, 311, 312, 314))
ORDER BY (dbo.Users.Name) DESC

 

[Andrzejek]

the last time each user id last logged something per the maxtaken date

Something like:[pre]

SELECT MAX(DateTimeTaken) AS MaxTaken, ScannedByID
FROM dbo.ScanTrackImage
GROUP BY ScannedByID[/pre]

But, if you keep adding fields to this Select, you will get inccorect results.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Thanks that worked. I see what you mean, adding fields makes for bad results.

Thanks

 

[Andrzejek]

If you need to add fields, you can. Just use the Select I gave you as a sub-query or use it as a separate 'table'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson