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
- 12 Answers
- 15030 Views
- CTS, I also Faced
- E-Mail Answers
Answer Posted / tdguy
SEL A.STUDENTID,A.STUDENTNAME,
CASE WHEN A.HIGHSUBJ='SUBJECT1'
THEN B.SUBJECT1
ELSE C.SUBJECT1
END AS SUBJECT1,
CASE WHEN A.HIGHSUBJ='SUBJECT2'
THEN B.SUBJECT2
ELSE C.SUBJECT2
END AS SUBJECT2,
CASE WHEN A.HIGHSUBJ='SUBJECT3'
THEN B.SUBJECT3
ELSE C.SUBJECT3
END AS SUBJECT3
FROM
(SEL STUDENTID,STUDENTNAME,SUBJECT1 ,SUBJECT2 ,SUBJECT3,
CASE WHEN SUBJECT1 > SUBJECT2 AND SUBJECT1 > SUBJECT3
THEN 'SUBJECT1'
WHEN SUBJECT2 > SUBJECT3
THEN 'SUBJECT2' ELSE 'SUBJECT3'
END AS HIGHSUBJ
FROM STUD1) A
INNER JOIN
(SEL STUDENTID,STUDENTNAME,SUBJECT1 ,SUBJECT2 ,SUBJECT3
FROM STUD1) B
ON A.STUDENTID=B.STUDENTID
INNER JOIN
(SEL STUDENTID,STUDENTNAME,NULL AS SUBJECT1 ,
NULL AS SUBJECT2 ,
NULL AS SUBJECT3 FROM STUD1) C
ON B.STUDENTID=C.STUDENTID
ORDER BY A.STUDENTID
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
Syntax for case when statement?
What are the functions performed by bynet?
What is teradata and why it is used?
Difference between star and snowflake schemas?
Can you connect multiload from ab initio?
How to load specific no.of records using bteq, or fastload,or multiload
How would you load a very large file in teradata in general?
Steps to create a data model?
Highlight the need for Performance Tuning.
What do you mean by teradata intelliflex?
Difference between stored procedure and macro?
What are the different table types that are supported by teradata?
What are tpump utility limitations?
how do you manage the production space. what are the proactive methods you can take ?
What do you mean by fastexport in teradata?
