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
- 15036 Views
- CTS, I also Faced
- E-Mail Answers
Answer Posted / mahesh raja
SEL STUDENTID,STUDENTNAME,MARKS, SBJ,RANK()OVER (PARTITION
BY STUDENTID ORDER BY MARKS DESC ) AS RANK1 FROM
(
SEL STUDENTID,STUDENTNAME,SUBJECT1 AS MARKS, 'SUB1' AS SBJ
FROM STUD1
UNION
SEL STUDENTID,STUDENTNAME,SUBJECT2 AS MARKS, 'SUB2' AS SBJ
FROM STUD1
UNION
SEL STUDENTID,STUDENTNAME,SUBJECT3 AS MARKS, 'SUB3' AS SBJ
FROM STUD1
)A QUALIFY RANK1=1
| Is This Answer Correct ? | 9 Yes | 0 No |
Post New Answer View All Answers
What happens in a conflict? How do you handle that?
What are the steps involved in the process flow of the sql statement through channel attached system?
What is the difference between fastload and multiload? Which one is faster?
Why do you get spool space errors? How do trouble-shoot them?
List out teradata data types?
Explain and compare pros and cons of start schemas?
What is the purpose of upsert command?
What is teradata?
How do you do backup and recovery in teradata?
What is stored procedure in teradata?
How to view every column and the columns contained in indexes in teradata?
What is spool space?
Highlight the advantages of PPI(Partition Primary Index).
Difference between star and snowflake schemas?
What are different table types used in teradata?
