Find the first result of a given day 2

[skatebkp]

Hi all,

First time poster needed some help with a query.

I have a query which displays the time each of my students logged in on a selected given date, the problem is that sometimes they will change computer and they will have two login times,

such as:

g smith 20/08/2010 11:00
g smith 20/08/2010 1130

I would like returned just the first login time for each user.

For this I thought a simple MIN function would solve the problem but the query still returns both results, here is my try:

SELECT [ADEReport].[dbo].[User].[JID]

,MIN(CONVERT(FLOAT, ChangeTime, 114)) as TimeLoggedIn


FROM [ADEReport].[dbo].[PresenceChange]JOIN [ADEReport].[dbo]. [User]
ON [ADEReport].[dbo].[PresenceChange].[UID] = [ADEReport].[dbo].[User].[UID]

AND CONVERT(CHAR(10),ChangeTime,120) = '2010-08-20'
AND PresenceStatus = 'online'
group by ChangeTime,JID
order by JID asc


I thought converting to a float might help but still no luck.

Any ideas where i'm going wrong?

 

[PHV]

Replace this:
group by ChangeTime,JID
with this:
group by JID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skatebkp]

I don't have access to my code right now but would'nt your solution return me an aggregate function error if I don't include ChangeTime.

 

[JarlH]

The general group by is: each column reference in the select list must either identify a grouping column or be the argument of a set function.

In your case, the minimum ChangeTime (for a date) is what you're looking for, i.e. do not group by that column!

 

[skatebkp]

you're right, I just hope that is not solution because I played around with this code so often I can't believe I did'nt try this before.

I'll let you know the result.

 

[skatebkp]

Dear me.... removing ChangeTime from group by solved all my problems.

Thank you very much guys, i'll try not to ask such a stupid question next time!