• vshiva2379 (7/28/2013)


    Execuse me

    Since you did not understand i how given that data and more over i do not want to disclose my original data i have given that, if you insist then have a look

    Create table Student(Studentid int,name varchar(50),maths int,science int,com int)

    insert into Student values(1,'Allen',40,50,89)

    insert into Student values(2,'John',70,90,89)

    insert into Student values(1,'Nick',90,80,89)

    Thank you

    SELECT Name, x.*

    FROM Student

    CROSS APPLY (

    SELECT TOP 1 * FROM (VALUES (maths,'maths'),(science,'science'),(com,'com')) d (Result,subject) ORDER BY Result DESC

    ) x


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]