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

Question

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

How can your function return FunctionNbr with value 00000001 anyway? It's not in your IN clause... Please provide consistent information in your question! — Thorsten Dittmar
– Thorsten Dittmar, Commented Jan 27, 2014 at 15:03
You shouldn't be using DISTINCT and GROUP BY in the same SELECT clause. They do the same/similar things and you're giving them conflicting parameters. I am pretty sure that it's undefined what happens to SQL in this case. — RBarryYoung
– RBarryYoung, Commented Jan 27, 2014 at 15:06
@Dittmar mistake of mine. Tried to do it with easy example but forgot to change the query. Will do so directly.@RBarryYoung removed the distinct to test your statement but the output still remains the same. — User999999
– User999999, Commented Jan 27, 2014 at 15:07
try: SELECT DISTINCT FunctionNbr,FunctionDesc, MAX(CAST(date_altered AS DATE)) it is possible that the dates are stored as text — MarkD
– MarkD, Commented Jan 27, 2014 at 15:10
@MarkD You're right! the dates were stored as text. The daily update apparently added a random amount trailing spaces. example '2013-12-20 '. How stupid of me to look past the most obvious thing.... Can you post your answer so i can approve it? — User999999
– User999999, Commented Jan 27, 2014 at 15:15

MarkD · Accepted Answer · 2014-01-27 15:53:19Z

1

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

answered Jan 27, 2014 at 15:53

MarkD

5,3261 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related