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
- 15037 Views
- CTS, I also Faced
- E-Mail Answers
Answer Posted / yuvaevergreen
CREATE TABLE STUD1
(
STUDENTID CHAR(10), STUDENTNAME CHAR(10),
SUBJECT1 INTEGER, SUBJECT2 INTEGER,
SUBJECT3 INTEGER
)PRIMARY INDEX( STUDENTID );
INSERT INTO STUD1 VALUES ('1','A1',2,7,1);
INSERT INTO STUD1 VALUES ('2','A2',1,6,2);
INSERT INTO STUD1 VALUES ('3','A3',3,5,3);
INSERT INTO STUD1 VALUES ('4','A4',4,4,7);
INSERT INTO STUD1 VALUES ('5','A5',5,3,6);
QUERY TO FETCH THE STUDENTS TOPPED IN ALL THE THREE SUBJECTS
SEL STUDENTID,STUDENTNAME,SUBJECT1,SUBJECT2,SUBJECT3
FROM
(
SEL STUDENTID,STUDENTNAME,SUBJECT1,SUBJECT2,SUBJECT3,
RANK () OVER (ORDER BY SUBJECT1 DESC) as SUB1RANK,
RANK () OVER (ORDER BY SUBJECT2 DESC) as SUB2RANK,
RANK () OVER (ORDER BY SUBJECT3 DESC) as SUB3RANK
FROM STUD1 QUALIFY (SUB1RANK=1 OR SUB2RANK=1 OR SUB3RANK=1 )) A
| Is This Answer Correct ? | 7 Yes | 4 No |
Post New Answer View All Answers
What do you mean by teradata intellicloud?
Comment whether bottleneck is an error or not.
What is meant by a node?
What are the different methods ot loading a dimension table? A fact table etc?
What are the different functions included in the server software?
what are the day to day activities of teradata DBA ?
What are the various etl tools in the market?
If a Node is busy what are the steps you can take to avoid ?
List out all forms of LOCKS that are available in Teradata.
What are the different softwares used with their functions in teradata?
How do you do backup and recovery in teradata?
What is the multi-insert?
Can we collect statistics on table level?
What is real time and near real time data warehousing?
What are the different table types that are supported by teradata?
