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
- 15035 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 partitioned primary index (ppi)?
What is the meaning of Caching in Teradata?
What are the joins in teradata?
Explain teradata architecture?
What is a node in teradata? Explain
What is the difference between global temporary tables and volatile temporary tables?
what is object level locking ? where do appear this type of locking ?
what is sysdba and sysdbc ? which has high priority ?
What are the functions involved in shared information architecture?
Explain and compare pros and cons of start schemas?
Highlight the differences between Primary Key and Primary Index.
What tools would you use for that?
What does Amp contain and what are all the operations that it performs?
What are the primary characteristics of the Teradata.
What are the available primary index types in teradata.
