Post reply

Help on query

  • August 19, 2013 at 4:31 am

    #1642610

    jethrow (8/19/2013)

    Can you provide more info on input & desired output?

    Hi jethrow,

    are you talking with me?

    I just ask vignesh, if mark1,mark2 and mark3 have same values, what result he wants to get?

  • August 19, 2013 at 4:43 am

    #1642611

    @jeff Moden

    Case is doing good ..

    but that question was not for the case solution. that's for Erland's Query...

  • August 19, 2013 at 4:44 am

    #1642612

    Hi jethrow ..

    if the all the marks are same need to show all the column names

  • August 19, 2013 at 5:01 am

    #1642617

    vignesh.ms (8/19/2013)

    Hi jethrow ..

    if the all the marks are same need to show all the column names

    Here's a CROSS APPLY VALUES version. CAV is a longhand UNPIVOT, it's a little faster - but not as fast as CASE can be.

    SELECT Name, x.MarkNames

    FROM #sample

    CROSS APPLY (

    SELECT MarkNames = STUFF(

    (SELECT ',' + d.MarkName

    FROM (

    SELECT cav.MarkName, r = DENSE_RANK() OVER(ORDER BY BestMark DESC)

    FROM (VALUES (Mark1,'Mark1'),(Mark2,'Mark2'),(Mark3,'Mark3')) cav (BestMark, MarkName)

    ) d

    WHERE r = 1

    ORDER BY d.MarkName

    FOR XML PATH(''))

    ,1,1,'')) x

    ORDER BY Name;

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 19, 2013 at 6:33 am

    #1642644

    vignesh.ms (8/19/2013)

    @Jeff Moden

    Case is doing good ..

    but that question was not for the case solution. that's for Erland's Query...

    Understood. I just wanted to make sure that you weren't overlooking the possibility of the CASE solution. As you've stated, though, it won't show ties. Something like what Erland or Chris have done should do it for you.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 19, 2013 at 6:34 am

    #1642645

    ;with cte as(

    select name, Markname,Marks from(

    select name,Mark1 ,

    mark2 ,

    mark3

    from sample

    ) p unpivot

    (Marks for Markname in (Mark1,Mark2,Mark3))

    as unpvt

    )

    select name,Markname from cte

    where exists (

    select 1 from cte a

    where a.name=cte.name

    group by name

    having Max(a.Marks)=cte.Marks)

  • August 19, 2013 at 6:42 am

    #1642651

    ;with cte as (select name, Markname,Marks,RANK() over (Partition by name order by Marks desc) as rm from(

    select name,Mark1 ,

    mark2 ,

    mark3

    from sample

    ) p unpivot

    (Marks for Markname in (Mark1,Mark2,Mark3))

    as unpvt)

    select name,Markname from cte where rm=1

  • August 19, 2013 at 7:11 am

    #1642671

    THANKS EVERY BODY

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply