StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMax() Behavior Different Between MS Access and SQL server
text
Body
copied!<p>So I am in the process of rewriting an MS Access application to a SQL server backend. I am struggling with some unusual behavior in the way Access is using <code>MAX()</code> and how SQL Server is doing it. Maybe it is lack of sleep but I have been staring at this for hours and I can't figure out why the SQL server results are different.</p> <p>Sample Data:</p> <pre><code>Acct ByUser TranType TranID AddID ClearTime TranTime 12345678 CZ12 W 55545124 CZ36 12/12/2011 9:45:31 AM 12/12/2011 9:45:31 AM 12345678 CZ24 W 55545124 CZ36 12/12/2011 10:01:26 AM 12/12/2011 10:01:26 AM 12345678 CZ36 W 55545124 CZ36 12/12/2011 9:45:31 AM 12/12/2011 9:45:31 AM 12345678 MG12 W 55545124 CZ36 12/12/2011 10:48:43 AM 12/12/2011 10:48:43 AM 12345678 CZ25 W 55545124 CZ36 12/12/2011 9:45:31 AM 12/12/2011 9:45:31 AM </code></pre> <p>MS Access query:</p> <pre><code>SELECT Acct, TypeID, TranType , Max(TranTime) AS MaxOfTranTime , AddID , Max(ClearTime) AS MaxOfClearTime FROM Cleared WHERE ( ((ByUser) Like "CZ*" Or (ByUser) Like "TR*" Or (ByUser) Like "RR*" Or (ByUser) Like "MG*" Or (ByUser) Like "RN*" Or (ByUser) Like "PS*" Or (ByUser) Like "OP*" Or (ByUser) Like "JA*" Or (ByUser) Like "IC*" Or (ByUser) Like "IB*" Or (ByUser) Like "FO*" Or (ByUser) Like "DV*" Or (ByUser) Like "CD*" Or (ByUser) Like "BO*" Or (ByUser) Like "2D*") ) GROUP BY Acct, TypeID, TranType, AddID </code></pre> <p>Results in Access:</p> <pre><code>Account TranID TranType MaxOfTranTime AddID MaxOfClearTime 12345678 55545124 W 12/12/2011 10:48:43 AM CZ36 12/12/2011 9:45:31 AM </code></pre> <p>The query in SQL server is slightly different in that my ByUsers are stored in a table to minimize typing in tons of queries.</p> <pre><code>SELECT C.Acct , C.RequestId , C.TypeCode , Max(C.TranTime) as MaxTranTime , C.AddUserId , Max(C.ClearDate) As MaxClearDate FROM Cleared C WHERE EXISTS (SELECT * FROM UserIdFilter U WHERE ByUserId LIKE U.UserId) GROUP BY Acct, RequestId, TypeCode, AddUserId </code></pre> <p>The SQL Server results are:</p> <pre><code>Account TranID TranType MaxOfTranTime AddID MaxOfClearTime 12345678 55545124 W 12/12/2011 10:48:43 AM CZ36 12/12/2011 10:48:43 AM </code></pre> <p>When I go through this it seems to me that SQL server is correct in getting the MaxOfClearTime of 12/12/2011 10:48:43 AM but am I missing something? Does Access process <code>MAX()</code> differently? I feel like I am losing my mind with this so any other eyes on this would be great. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload