Query for calculating greatest.....

Hai all,
i hav an table vth four columns

Code:

---

name  sub1  sub2  sub3

raj    40        50      87
sam    34        67      14

---

my query is tat i hav to calculate the large value for raj and sam...
can any one suggest vth query...?
thanks,

Hi all.
What do you mean with "the large value for raj and sam"?
The records of column name that have more characters? Or what?
It's quite hard to suppose what you're looking for...

i hav to show ouput as...

name subname
raj 87
sam 67

OK, now it's clear :)
You want to extract the records in alphabetical order, so

Code:

---

SELECT name, subname
  FROM YOUR_TABLE
 ORDER BY name

---

is all you need.

I think what he meant was that he wanted to show each persons highest marks...That is to show whoch subject each person is strongest in...Not meerly show it in alphabetical order..Right??

The "problem" is that the three grades are all IN THE SAME ROW.

This is a database DESIGN problem.

So the first questions are:

1) Can you change the Database? (If not Why not?)

2) WHY was such a bad design chosen in the first place???

Why not create 3 queries..
The first displays only sub1 and that too only if its value is higher than sub 2 and 3 else is blank...
The second displays only sub2 and that too only if its value is higher than sub1 and 3 else is blank...
The third displays only sub3 and that too only if its value is higher than sub 1 and 2 else is blank...
Finally
Create a third query which combines the data from all three queries and thus displays only the highest value,....
It's pain staking and may create other problems but atleast u don't have to change the table structure...

I agree that such design is somewhat strange to begin with, and it does sound "school-workish", anyways....
If you the number of columns are fixed, you could for example use CASE to solve the problem, but it isn't a pretty solution:

Code:

---

SELECT CASE
        WHEN  sub1 < sub2 THEN
        CASE
                WHEN  sub2 < sub3 THEN sub3 --sub3 is highest
                ELSE sub2 --sub2 is highest
        END
        ELSE
                CASE
                        WHEN sub1 < sub3 THEN sub3 --sub3 is highest
                        ELSE sub1 --sub1 is higest
                END
END
FROM <YOUR FROM STATEMENT HERE>

---

If you use SQL Server 2005+ then you could likely unpivot the table as well, and select it out based on that for a much more elegant solution:

Code:

---

WITH CTE AS (
        SELECT [name], ColumName, UnPivotScore
        FROM
        (
                SELECT [name], sub1, sub2, sub3
                FROM <YOUR TABLE HERE>) P
        UNPIVOT
        (
                UnPivotScore FOR ColumName IN (sub1, sub2, sub3)
        ) AS UP
       
)
SELECT [name], MAX(UnPivotScore)
FROM CTE
GROUP BY [name]

---

Both should return
raj 87
sam 67

edit: I'm sure there are many other solutions as well, which is why I personally like SQL. Many paths to the same result :D