SEL STUDENTID,STUDENTNAME,MARKS, SBJ,RANK()OVER (PARTITION BY STUDENTID O

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

12 Answers
18090 Views
CTS, I also Faced
E-Mail Answers

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 object level locking ? where do appear this type of locking ?

3506

What do you mean by teradata intellicloud?

1042

How to explain project Architecture and flow in teradata interviews?Can please anyone help on this? Am new to teradata.

4379

List out some of the primary characteristics of teradata.

1017

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

1086

Explain amp in teradata?

1091

Explain the teradata primary index mechanics in detail with a diagram.

982

what is sysdba and sysdbc ? which has high priority ?

3838

Highlight the advantages of PPI(Partition Primary Index).

1080

Explain Teradata performance tuning and optimization?

1177

why use references rather than pointers in the public api, particularly for arguments which are modified?

1013

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

2021

If Fast Load Script fails and only the error tables are made available to you, then how will you restart?

1159

What are the updated features of teradata?

1047

What is a dimension table?

1072