Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


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

Answers were Sorted based on User's Feedback



i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

Answer / hariharan

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

i have column like below studentid studentname sub1 sub2 sub3 1 aaa 40 70 ..

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

Post New Answer



More Teradata Interview Questions

What is multi insert?

0 Answers  


What are the different design perspectives used in teradata?

0 Answers  


How many sessions of MAX is PE capable of handling at a particular time?

0 Answers  


Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?

9 Answers   IBM,


Hi Friends, Please send me Teradata Materials for certifications Thanks in advance !!

0 Answers   HP,






What are the main components of teradata system?

0 Answers  


Is PK concept available in Teradata. If it is how can we create Primary Key for a table in TD

6 Answers   Wipro,


Why is the case expression used in teradata?

0 Answers  


What is called partitioned primary index (ppi) and discuss the advantages of using it in a query?

0 Answers  


I want to write one query How to get Matched records and unmatched records in table?

4 Answers   TCS,


what are the file sizes in your project?

2 Answers  


In general, how do you optimze any sql in teradata?

0 Answers  






Categories