how to select the maximum value from multiple columns in sql server 2000

Question

0.00/5 (No votes)

See more:

SQL-server-2000

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 4-Sep-12 23:51pm

MurugappanCTS

Updated 20-Oct-20 8:56am

Add a Solution

5 solutions

Solution 1

try below query:-

SQL

select 
case when marks1 > marks2 and marks1 > marks3 then marks1
     when marks2 > marks1 and marks2 > marks3 then marks2
else marks3 
end
from
exam

Posted 4-Sep-12 23:55pm

ssd_coolguy

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]

Posted 20-Oct-20 8:57am

Member 14970667

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

Solution 2

Please use this user defined table valued function to split your string and then apply max.

SQL

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

SQL

Select MAX(dbo.Split("YourString"))

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

Please vote if it satisfied..!!

Posted 5-Sep-12 0:07am

Aadhar Joshi

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'

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2012-09-05T01:51:00

Solution 3

It should works:

SQL

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 ;)

Posted 5-Sep-12 1:51am

Maciej Los

Comments

Sandeep Mewara 5-Sep-12 7:57am

My 5! Good one Mac.

Maciej Los 5-Sep-12 7:58am

Thank you, Sandeep ;)

Prasad_NC · Accepted Answer · 2014-02-06T20:57:00

Solution 4

SQL

SELECT (select MAX(myval) from (values (mark1),(mark2),(mark3)) as D(myval)) AS 'MaxMarks'
   FROM
   YourTable

Posted 6-Feb-14 20:57pm

Prasad_NC