InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 16:10:27 GMT20http://www.sqlservercentral.com/Forums/Topic1071515-1291-1.aspxThanks once again. I was figuring out that and your post helped me.I have tweaked it a bit as max is working fine for date's stored in nvarchar columnhere is the final one.[code="sql"]SELECT DATATYPE,ISNULL(MAXNUMBER,'')+ISNULL(MAXVALUE,'')FROM(SELECT DataType,CAST(MAX(CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2)) ELSE NULL END) AS NVARCHAR(MAX))AS MaxNumber,MAX(CASE WHEN DataType NOT in ('CURRENCY','NUMBER') THEN VALUE ELSE NULL END) as MAXVALUEFROM GetMaxValuesGROUP BY DataType) A[/code]Thanks,RaviTue, 01 Mar 2011 13:41:33 GMTravi-441349http://www.sqlservercentral.com/Forums/Topic1071515-1291-1.aspxok then...do this... (use the query i sent you as a subquery...then collapse the columns) -- typing as i think[code="sql"]SELECT DataType, MAX(CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(MaxNumer as varchar)WHEN DataType in ('Date') THEN CAST(MaxDate as varchar)ELSE NULL END) as MaxFieldValueFROM (SELECT DataType,MAX(CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2)) ELSE NULL END) AS MaxNumber,MAX(CASE WHEN DataType in ('Date') THEN CAST(Value AS Datetime) ELSE NULL END) AS MaxDate,FROM GetMaxValuesGROUP BY DataType) T1GROUP BY DataType[/code]Tue, 01 Mar 2011 13:26:13 GMTChazManhttp://www.sqlservercentral.com/Forums/Topic1071515-1291-1.aspxThanks for the quick reply. That works. but the problem is I am getting the multiple columns. I think I need to figure out a way to merge these multiple columns in to one.Appreciate your help anyway.Thanks,RaviTue, 01 Mar 2011 13:16:23 GMTravi-441349http://www.sqlservercentral.com/Forums/Topic1071515-1291-1.aspxFirst off...yes this is the WRONG way to do things. You won't be able to do it in a single field, but you can do it with multiple fields (i think):Using your code sample, try the following for each type of field you want:[code="sql"]SELECT DataType,MAX(CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2)) ELSE NULL END) AS MaxNumber,MAX(CASE WHEN DataType in ('Date') THEN CAST(Value AS Datetime) ELSE NULL END) AS MaxDate,FROM GetMaxValuesGROUP BY DataType[/code]Hope this works!Tue, 01 Mar 2011 13:04:26 GMTChazManhttp://www.sqlservercentral.com/Forums/Topic1071515-1291-1.aspxHi,I have a particular requirement where there is a particular column in a table that stores all the data types. This column is defined as nvarchar(max). This column holds values for datatypes BOOLEAN, DATE,TEXT, CURRENCY and NUMBER. There are other columns in this table on which I need to do group by. Based on the other columns we will only get one data type i.e CURRENCY or NUMBER or DATE or TEXT.The sample data is attached. I want to pick maximum of currency, numbers and date based on what is the highest. for text max on character sorting.Initially i triedselect datatype,max(value) from getmaxvalues group by datatypebut the above query gives the character max even for currency, numbers. For date it somehow works fine.then i tried the below query. it gives conversion errorSELECT DataType,MAX( CASE WHEN DataType in ('CURRENCY','NUMBER') THEN CAST(Value AS DECIMAL(18,2)) ELSE Value END) AS MaxValFROM GetMaxValuesGROUP BY DataTypeI modified it as below. but still gives conversion errorselect DataType,MAX(CASE WHEN ISNUMERIC(VALUE)=1 THEN CAST(Value as DECIMAL(18,2)) WHEN ISDATE(VALUE)=1 THEN CAST(Value as DATE) ELSE VALUE END) FROMGetMaxValuesgroup by datatypeI know that what I am trying to do is not the correct way but not sure on how to get this done.Any help/pointers would be greatly appreciatedThanks,RaviTue, 01 Mar 2011 12:38:12 GMTravi-441349