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
- 15008 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 |
How do you load Multiple files to a table by using fastload scripts?
What is the particular designated level at which a LOCK is liable to be applied in Teradata?
What's the syntax of sub string?
What is use of compress in terdata?Explain?
Explain and compare pros and cons of snow flake schemas?
What are the different table types that are supported by teradata?
What are the newly developed features of Teradata?
What are the different table types supported by teradata?
How does hashing happens in teradata?
What are the design features involved in teradata?
When tpump is used instead of multiload?
Why Multiload does not supports USI and supports NUSI?
Teradata 
