Click here to Skip to main content
14,665,306 members
Rate this:
Please Sign up or sign in to vote.
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 8:56am

Rate this:
Please Sign up or sign in to vote.

Solution 3

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 ;)
Rate this:
Please Sign up or sign in to vote.

Solution 4

SELECT (select MAX(myval) from (values (mark1),(mark2),(mark3)) as D(myval)) AS 'MaxMarks'
   FROM
   YourTable
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 5

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 5 days ago
   
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
Rate this:
Please Sign up or sign in to vote.

Solution 2

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100