1. Categories >> Software >> Data Warehouse >> Teradata
  2. Suggest New Category

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

Question Posted / mahesh raja

Answer Posted / 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



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

What is partitioned primary index (ppi)?

579

How is MLOAD Teradata Server restarted after execution?

664

Can we have an unconnected lkp to lookup a db2 record against a teradata record? Doesnt seem to work. I could be wrong

641

Highlight the limitations of TPUMP Utility.

617

What is smp and mpp platforms?

535




What is inner join and outer join?

624

how many modules are there in telecome domain?how to explain the architecture?

1563

Explain the term 'primary key' related to relational database management system?

611

What is a three-tier data warehouse?

639

Differentiate primary key and partition key?

590

What is stored procedure in teradata?

598

What is the meaning of Caching in Teradata?

699

What is a clique?

595

What is collect statistics?

600

Describe primary index in teradata?

593