Query for calculating greatest.....

[chinnaraj]

Hai all,
i hav an table vth four columns


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,

[davide++]

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...

[chinnaraj]

i hav to show ouput as...

name subname
raj 87
sam 67

[davide++]

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


SELECT name, subname
FROM YOUR_TABLE
ORDER BY name


is all you need.

[Suraj...]

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??

[TheCPUWizard]

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???

[Suraj...]

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...

[Alsvha]

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:


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:


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