Click here to Skip to main content
14,690,608 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am having three column named
mark1,mark2,mark3 (for eg:80,86,69) , i need to select the maximum values from these three values and put them in a new column what is the sql query for selecting maximum values among multiple columns please hellp me........
Posted
Updated 20-Oct-20 9:56am

It should works:
SELECT MAX(T.Age) AS MaxOfAge
FROM (
    SELECT mark1 AS Age
    FROM YourTable 
    UNION ALL
    SELECT mark2 AS Age
    FROM YourTable
    UNION ALL
    SELECT mark3 As Age
    FROM YourTable) AS T


Idea: fetch data from 3 different columns in to one and then get the maximum ;)
   
Comments
Sandeep Mewara 5-Sep-12 7:57am
   
My 5! Good one Mac.
Maciej Los 5-Sep-12 7:58am
   
Thank you, Sandeep ;)
SELECT (select MAX(myval) from (values (mark1),(mark2),(mark3)) as D(myval)) AS 'MaxMarks'
   FROM
   YourTable
   
try below query:-
select 
case when marks1 > marks2 and marks1 > marks3 then marks1
     when marks2 > marks1 and marks2 > marks3 then marks2
else marks3 
end
from
exam
   
I would think that you would just use columns in a subquery:


select *, 'MaxValue' = (select max(value) from (select 'value' = mark1 union all select mark2 union all select mark3)i0)
from [tablename]
   
Comments
CHill60 21-Oct-20 9:00am
   
Which is exactly the technique used in Solution 3 from eight years ago. I applaud your desire to help, but please make sure you are bringing something new to a thread before posting additional solutions
Please use this user defined table valued function to split your string and then apply max.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split] 
(    
 @String varchar(max) 
,@Delimiter char 
) 
RETURNS @Results table 
( 
 Ordinal int 
,StringValue varchar(max) 
) 
as 
begin 
 
    set @String = isnull(@String,'') 
    set @Delimiter = isnull(@Delimiter,'') 
 
    declare 
     @TempString varchar(max) = @String 
    ,@Ordinal int = 0 
    ,@CharIndex int = 0 
 
    set @CharIndex = charindex(@Delimiter, @TempString) 
    while @CharIndex != 0 begin      
        set @Ordinal += 1        
        insert @Results values 
        ( 
         @Ordinal 
        ,substring(@TempString, 0, @CharIndex) 
        )        
        set @TempString = substring(@TempString, @CharIndex + 1, len(@TempString) - @CharIndex)      
        set @CharIndex = charindex(@Delimiter, @TempString) 
    end 
 
    if @TempString != '' begin 
        set @Ordinal += 1  
        insert @Results values 
        ( 
         @Ordinal 
        ,@TempString 
        ) 
    end 
 
    return 
end

To test your code
Select MAX(dbo.Split("YourString"))


Note: Cast or Convert operator required in case of string..

Please vote if it satisfied..!!
   
Comments
ssd_coolguy 5-Sep-12 6:18am
   
i think it's not a single string. it's 3 different columns.
your function is correct when input is like '90,75,80'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900