Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 17928 Views
- CTS, I also Faced
- E-Mail Answers
Answer Posted / shaik mahamad rafi
Lets Try This
sel t.id ,t.marks,rank(t.marks) as "r" from
(
sel a.studentid,max(a.subject1) from stud1 a group by a.studentid
union
sel a.studentid,max(a.subject2) from stud1 a group by a.studentid
union
sel a.studentid,max(a.subject3) from stud1 a group by a.studentid
)as t (id,marks)
group by id qualify r=1;
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);
Table :
STUDENTID|STUDENTNAME|SUBJECT1|SUBJECT2|SUBJECT3
5 |A5 |5 |3 |6
3 |A3 |3 |5 |3
1 |A1 |2 |7 |1
4 |A4 |4 |4 |7
2 |A2 |1 |6 |2
O/p:
id |marks
1 |7
2 |6
3 |5
4 |7
5 |6
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
What is the purpose of upsert command?
What are default access rights in teradata?
i learn teradata,it is so intersting,now i want teradata certifications ,so any 1 help me to get teradata certifications TD 12 ? email:kdcrazyy@gmail.com
What is meant by a Parsing Engine?
What is bteq script in teradata?
Mention a few of the ETL tools that come under Teradata.
What do you mean by teradata intellicloud?
Backup Script was blocked you are unable to archive the data now. how do you analyze it and where do you identify ?
What are the joins in teradata and how many types of joins are there in teradata?
How to write the query . eid enm doj dob i want to display the names who worked more than 25 years .
Give the sizes of SMALLINT, BYTEINT and INTEGER.
During the Display time, how is the sequence generated by Teradata?
What is the difference between teradata and basic rdbms?
How many sessions of MAX is PE capable of handling at a particular time?
What is primary index and secondary index?
