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
- 15139 Views
- CTS, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / tdguy
SEL A.STUDENTID,A.STUDENTNAME,
CASE WHEN A.HIGHSUBJ='SUBJECT1'
THEN A.SUBJECT1 ELSE C.SUBJECT1
END AS SUBJECT1,
CASE WHEN A.HIGHSUBJ='SUBJECT2'
THEN A.SUBJECT2 ELSE C.SUBJECT2
END AS SUBJECT2,
CASE WHEN A.HIGHSUBJ='SUBJECT3'
THEN A.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,NULL AS SUBJECT1 ,
NULL AS SUBJECT2 ,NULL AS SUBJECT3 FROM STUD1) C
ON A.STUDENTID=C.STUDENTID
ORDER BY A.STUDENTID
| Is This Answer Correct ? | 12 Yes | 0 No |
Answer / 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 |
Answer / 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 |
sel
studentid,
studentname,
case
when subject1>subject2 and subject1>subject3
then subject1
when subject2>subject1 and subject2>subject3
then subject2
when subject3>subject1 and subject3>subject2
then subject3
end as high_score,
case
when high_score = subject1
then 'subject1'
when high_score = subject2
then 'subject2'
when high_score = subject3
then 'subject3'
end as subject_name
from stud1
order by 1,2
;
| Is This Answer Correct ? | 3 Yes | 0 No |
Answer / yuvaevergreen
typo error in the above answer, its not "query to fetch the students topped in all the three"...its just students topped in each subjects....its typo
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / 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 |
Answer / 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 |
Answer / shanmukh
SELECT STUDENTID, STUDENTNAME,
CASE WHEN SUBJECT1>SUBJECT2 AND SUBJECT1>SUBJECT3
THEN SUBJECT1 END AS SUBJECT1,
CASE WHEN SUBJECT2>SUBJECT1 AND SUBJECT2>SUBJECT3
THEN SUBJECT2 END AS SUBJECT2,
CASE WHEN SUBJECT3>SUBJECT1 AND SUBJECT3>SUBJECT2
THEN SUBJECT3 END AS SUBJECT3
FROM STUD1;
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / alok
Just optimized Hariharan's And
SEL A.STUDENTID,A.STUDENTNAME,
CASE WHEN A.HIGHSUBJ='SUBJECT1'
THEN A.SUBJECT1
END AS SUBJECT1,
CASE WHEN A.HIGHSUBJ='SUBJECT2'
THEN A.SUBJECT2
END AS SUBJECT2,
CASE WHEN A.HIGHSUBJ='SUBJECT3'
THEN A.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
ORDER BY A.STUDENTID ;
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / vishvajeet solanke
TRY THIS ONE..ITS SIMPLE
CREATE VOLATILE TABLE TEST
(e_NAME VARCHAR(10),SUB1 INTEGER,SUB2 INTEGER,SUB3 INTEGER)
ON COMMIT PRESERVE ROWS;
INSERT INTO TEST (e_NAME,SUB1,SUB2,SUB3) VALUES('AAA',85,95,92);
INSERT INTO TEST (e_NAME,SUB1,SUB2,SUB3) VALUES('BBB',75,67,82);
SEL E_NAME,MAX(SUBJECT) FROM
(SEL E_NAME,SUB1 AS SUBJECT
FROM TEST
UNION
SEL E_NAME,SUB2 AS SUBJECT
FROM TEST
UNION
SEL E_NAME,SUB3 AS SUBJECT
FROM TEST
) TS
GROUP BY 1
| Is This Answer Correct ? | 0 Yes | 0 No |
What are the types of tables in Teradata
22 Answers Cap Gemini, Cognizant, Infosys,
Syntax for case when statement?
What are default access rights in teradata? What explicit right can be given to a user?
Where is teradata rdbms used?
How many tables can you join in v2r5?
What is the use of fallback?
What is the process to restart the multiload if it fails?
IS IT POSSIBLE THAT THERE R TWO PRIMARY KEY WILL BE IN A SAME TABLE?PLZ HELP ME BY GIVING THE ANSWER.THANK U.
how do we write scripts in unix how to execute scripts in real time anybody please needfull or give me number i will cal u
How to copy teradata scripts from ur remote desktop to server machine?
How do you Generate Sequence in Teradata?
Difference between inner join and outer join?
Teradata 
