How to select max row value from sql -server-2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

I have a table data like this,

Name m1 m2 m3 m4
-----------------

N1 34 36 37 58
N2 35 31 45 67
N3 34 65 37 34
N4 34 78 09 58
N5 34 47 0 18
N6 89 36 56 60

I need the result as

Name Maximum Mark
---------------
N1 58
N2 67
N3 65
N4 78
N5 47
N6 89

How to do this in sql -server 2005

Posted 19-Jan-12 17:37pm

sacraj

Add a Solution

4 solutions

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)

sahabiswarup · Answer 1 · 2012-01-19T19:01:00

You may try sql subquery.
here is an example

SQL

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name	Sales	Sales_Rank
Greg	50	1
Sophia	40	2
Stella	20	3
Jeff	20	3
Jennifer	15	5
John	10	6

original table is

Total_Sales

Name	Sales
John	10
Jennifer	15
Stella	20
Sophia	40
Greg	50
Jeff	20

Om Prakash Pant · Answer 2 · 2012-01-19T19:07:00

This may not be the optimim way but it gives the output you want....

SQL

select nname, max(M.MaximumMark) from (
select nname, m1 as MaximumMark from test_ union
select nname, m2 as MaximumMark from test_ union
select nname, m3 as MaximumMark from test_ union
select nname, m4 as MaximumMark from test_) AS M
GROUP BY M.nname

Costica U · Answer 3 · 2012-01-21T03:59:00

Here is another one

SQL

if object_id('tempdb..#temp') is not null
    drop table #temp

create table #temp
(
[Name] varchar(10),
[m1] int,
[m2] int,
[m3] int,
[m4] int
)

insert into #temp([Name],[m1],[m2],[m3],[m4])values('N1', 34, 36, 37, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N2', 35, 31, 45, 67)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N3', 34, 65, 37, 34)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N4', 34, 78, 09, 58)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N5', 34, 47, 0, 18)
insert into #temp([Name],[m1],[m2],[m3],[m4])values('N6', 89, 36, 56, 60)

-- 1. [Name] is unique
select  [Name],
        case
            when [m1] > [m2] and [m1] > [m3] and [m1] > [m4] then [m1]
            when [m2] > [m1] and [m2] > [m3] and [m2] > [m4] then [m2]
            when [m3] > [m1] and [m3] > [m2] and [m3] > [m4] then [m3]
            else [m4]
        end as MaximumMark
from    #temp

-- 2. [Name] is NOT unique
select  [Name],
        max(
            case
                when [m1] > [m2] and [m1] > [m3] and [m1] > [m4] then [m1]
                when [m2] > [m1] and [m2] > [m3] and [m2] > [m4] then [m2]
                when [m3] > [m1] and [m3] > [m2] and [m3] > [m4] then [m3]
                else [m4]
            end
            ) as MaximumMark
from    #temp
group
by      [Name]

Manoj Kumar Choubey · Answer 4 · 2012-01-21T05:34:00

Solution 4

I am not tested it but try this

Quote:
Select Name ,

iif(m1>m2,iif(m1>m3,iif(m1>m4,m1,m4),iif(m3>m4,m3,m4),iif(m2>m3,iif(m2>m4,m2,m4),iif(m3>m4,m3,m4))))) as Maximum Mark

From TableName Order By (m1+m2+m3+4)

Posted 21-Jan-12 5:34am

Manoj Kumar Choubey

How to select max row value from sql -server-2005

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0