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