Is there a bug in the max function 1

[jemahoney]

I am using the TSQL select statement:

SELECT RATE_TABLE_ID,"PROJECT" = RATE_KEY_VALUE1,"CODE" = RATE_KEY_VALUE2, "EFFECT_DATE" = MAX(EFFECT_DATE),RATE,"CODE_VALUE_DESC" = ISNULL(PJCODE.CODE_VALUE_DESC,"&quotFROM PJRATE
LEFT OUTER JOIN PJCODE ON PJCODE.CODE_TYPE = "0CDE" AND PJCODE.CODE_VALUE = LTRIM(RTRIM(RATE_KEY_VALUE2))
WHERE RATE_TABLE_ID = "INV" GROUP BY RATE_TABLE_ID,RATE_KEY_VALUE1,RATE_KEY_VALUE2,RATE,CODE_VALUE_DESC,RATE_TYPE_CD

the table I am querying has rates with efective dating. Therefore I am only interested in the rate with the most recent date. This query retrieves one record for each rate, therefore the max function is not working. The data type for effective_date is smalldatetime.

Is the mssql7.0.

There is a bug with mssql 7.0 with this function? If, why doesn't this query just return one record, the one with the most recent date.

 

[Cagliostro]

I think max function, is not so aplicable to data type.
You can use min(datediff(d,date1,getDate())) John Fill

ivfmd@mail.md

 

[tlbroadbent]

You can use max for character data.

The problem is in what you are asking SQL Server to do. When you Group By a list of columns, you'll get one record for every unique occurrence of all the columns in the Group By list - not just the date.

Perhaps, what you want is a query like this:

SELECT RATE_TABLE_ID, "PROJECT" = RATE_KEY_VALUE1, "CODE" = RATE_KEY_VALUE2, EFFECT_DATE ,RATE,"CODE_VALUE_DESC" = ISNULL(PJCODE.CODE_VALUE_DESC,"&quotFROM PJRATE
LEFT OUTER JOIN PJCODE ON PJCODE.CODE_TYPE = "0CDE" AND PJCODE.CODE_VALUE = LTRIM(RTRIM(RATE_KEY_VALUE2))
WHERE RATE_TABLE_ID = "INV" AND EFFECT_DATE = (Select MAX(EFFECT_DATE) FROM PJRATE) Terry

http://members.home.net/tlbroadbent/index.htm