CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2max
User999999
User999999

Reputation: 2520

SQL Server 2008 - MAX() - function produces invalid/unexpected output

Consider following query 

SELECT DISTINCT FunctionNbr,FunctionDesc, MAX(date_altered)
FROM Persontable
WHERE FunctionNbr IN ('00000001','00000002','00000003')
AND LEN(RTRIM(FunctionDesc)) > 0 
GROUP BY FunctionNbr,FunctionDesc

the persontable contains all the employees with their respective function. the date_altered may vary depending on changes made on SAP.

My expected output would be that i get a record for every employee with one of those functionNbr and with the same date_altered.

example of expected output:

FunctionNbr | FunctionDesc | date_altered
--------------------------------------------
00000001    | Function A   | 2014-01-01    (=row from employee 001 with functionNbr = 0000001 and date_altered = 2013-12-20)
00000001    | Function A   | 2014-01-01    (=row from employee 002 with functionNbr = 0000001 and date_altered = 2013-12-24)
00000001    | Function A   | 2014-01-01    (=row from employee 003 with functionNbr = 0000001 and date_altered = 2014-01-01)
00000002    | Function B   | 2013-12-13    (=row from employee 004 with functionNbr = 0000002 and date_altered = 2013-12-13)
00000002    | Function B   | 2013-12-13    (=row from employee 005 with functionNbr = 0000002 and date_altered = 2013-12-11)

yet my output looks as follows:

FunctionNbr | FunctionDesc | date_altered
--------------------------------------------
00000001    | Function A   | 2013-12-20    (=row from employee 001 with functionNbr = 0000001 and date_altered = 2013-12-20)
00000001    | Function A   | 2013-12-24    (=row from employee 002 with functionNbr = 0000001 and date_altered = 2013-12-24)
00000001    | Function A   | 2014-01-01    (=row from employee 003 with functionNbr = 0000001 and date_altered = 2014-01-01)
00000002    | Function B   | 2013-12-13    (=row from employee 004 with functionNbr = 0000002 and date_altered = 2013-12-13)
00000002    | Function B   | 2013-12-11    (=row from employee 005 with functionNbr = 0000002 and date_altered = 2013-12-11)

Question: In this case, Why doensn't the MAX() function always take the last date_altered

Note: For every employee there is only 1 row

Upvotes: 0

Views: 77

Answers (1)

MarkD
MarkD

Reputation: 5316

It is very possible that the table's dates are stored as text. Try this;

SELECT FunctionNbr,FunctionDesc, MAX(CAST(date_altered AS DATETIME)
FROM Persontable
WHERE FunctionNbr IN ('00000001','00000002','00000003')
AND LEN(RTRIM(FunctionDesc)) > 0 
GROUP BY FunctionNbr,FunctionDesc

I've removed the DISTINCT as correctly suggested by RBarryYoung

Upvotes: 1

Related Questions