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
- 17605 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 |
Why does varchar occupy 2 extra bytes?
If the PMON is not working then how do you manage and monitor all processes, resources and sessions etc.
What is meant by a Highest Cost Plan?
How to write the query . eid enm doj dob i want to display the names who worked more than 25 years .
What are the steps to create a data model?
What are the things to be considered while creating secondary index?
Differentiate database data and data warehouse data?
What are the components provided on node?
I want to load 1000 rcds using. FL for every 100 records there is a check point.But script failed at 120 records, when we are restarting the script, it starts from last ckpt, but Target table contains populated data but FL doesnt support existing data in target table. How can we load data in FL?
What do you mean by teradata sql assistant?
What is node? How many nodes and amps used in your previous project?
What is logical data model?
Teradata 
