Max Value Per Row from a set of 100 Columns in SQL?

Up vote 2 down vote favorite 1 share g+ share fb share tw.

While I have seen several answers to this question all of them tend to rely on on the greatest function which keeps coming back with a "Msg 195, Level 15, State 10, Line 1 'GREATEST' is not a recognized built-in function name. " I would like to know a way to build a query that for each row returns the artifact id, and the greatest value in the row along with the column it was in. Any suggestions would be welcome.

I've looked over quite a few example on this board and others but keep runnign across various errors. Attempted and Failed Code, shortened for veiwing pleasure: SELECT artifactID, GREATEST(V0,V1,V2,V3,V4,V5,V6...V98,V99) AS col_value, CASE GREATEST(V0,V1,V2,V3,V4,V5,V6...V98,V99) WHEN V0 THEN '0' WHEN V1 THEN '1' WHEN V2 THEN '2' WHEN V3 THEN '3' ... WHEN V99 THEN '99' END AS col_name FROM dbo. Theta Example of what I would like query to return: ArtifactID GreatestColumnValue ColumnValueCameFrom 00001 .330 59 00002 .89 89 ... 01000 .44 7 Thanks!

Sql return-value row maximum link|improve this question asked Apr 4 '11 at 20:15Artina111.

– Lamak Apr 4 '11 at 20:22 @Lamak - dbo.Theta... and Msg, Level.. SQL Server I guess – Richard aka cyberkiwi Apr 4 '11 at 20:26 / hope you are using SQL Server 2005+, otherwise.. a much longer answer will be needed :) – Richard aka cyberkiwi Apr 4 '11 at 21:02.

SELECT t. ArtifactID, c. Col_value, c.

Col_name FROM dbo. Theta t OUTER APPLY (SELECT TOP 1 * FROM ( SELECT '0', V0 UNION ALL SELECT '1', V1 UNION ALL --... SELECT '99', V99) X(col_name,col_value) ORDER BY col_value desc) C.

1, that is a very good answer... – Lamak Apr 4 '11 at 21:00 That worked perfectly! Thank you so much for your help! – Artina Apr 5 '11 at 12:34.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.