i have column like below

studentid studentname sub1 sub2 sub3
1 aaa 40 70 90
2 bbb 60 80 50
i want to execute every student highest mark in which subject
my o/p like below
studentid studentname sub3 sub2
1 aaa 90
2 bbb 80

Answer Posted / hariharan

sel
studentid,
studentname,
case
when subject1>subject2 and subject1>subject3
then subject1
when subject2>subject1 and subject2>subject3
then subject2
when subject3>subject1 and subject3>subject2
then subject3
end as high_score,
case
when high_score = subject1
then 'subject1'
when high_score = subject2
then 'subject2'
when high_score = subject3
then 'subject3'
end as subject_name
from stud1
order by 1,2
;

Is This Answer Correct ?    3 Yes 0 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

What are default access rights in teradata? What explicit right can be given to a user?

627


How to find duplicates in a table?

587


Is it necessary to add? Quit statement after a bteq query when I am calling it in a unix environment?

625


Difference between stored procedure and macro?

660


What is the syntax for case when statement?

566






Difference between inner join and outer join?

557


What is the particular designated level at which a LOCK is liable to be applied in Teradata?

575


Backup Script was blocked you are unable to archive the data now. how do you analyze it and where do you identify ?

1582


Explain the most common data types used in teradata?

567


How teradata makes sure that there are no duplicate rows being inserted when its a set table?

540


What is real time and near real time data warehousing?

646


What is a dimension table?

585


Hi send me the Teradata dumps to my id rajeshanantha@yahoo.co.in Thanks Rajesh. A

2879


What are default access rights in teradata?

600


Describe primary index in teradata?

559