SQL Server Performance

Max value of multiple rows...

Discussion in 'SQL Server 2005 General Developer Questions' started by i.netdeveloper, Jul 1, 2008.

  1. i.netdeveloper Member

    I have two tables A and B while joining them its resulting multiple row set as follows...
    Answer MaxAnswer
    3 1
    3 2
    3 3
    1 1
    1 2
    1 3
    2 1
    2 2
    2 3
    2 4
    2 5
    But I want to see the result set as
    Answer Max Answer
    3 3
    1 3
    2 5
    I have tried using Max() but it is getting the maximum of entire column, I tried count it is getting the total count.
    Here every answer is related to multiple answer types but i want to select maximum of that value.
    Its really urgent I should complete it today...I am reposting it again as I could't solve this.
    Thanks...
    i.netdeveloper, Jul 1, 2008
    #2

  2. Madhivanan Moderator

    Select answer,max(maxanswer) as maxanswer from table
    group by answer
    Madhivanan, Jul 1, 2008
    #3

  3. i.netdeveloper Member

    Thanks for replying to my post.
    By doing select answer, max(maxanswer) as maxanswer from table
    groupby answer will get the overall maximum value of the column which is not what i am looking for.
    i.netdeveloper, Jul 1, 2008
    #4

  4. Madhivanan Moderator

    Did you try my solution?
    It should work
    Madhivanan, Jul 2, 2008
    #5

  5. i.netdeveloper Member

    I realised that i was doing something wrong in group by, after reading ur post.
    I was able to fix the problem.
    Thanks for the help.
    i.netdeveloper, Jul 2, 2008
    #6

  6. Madhivanan Moderator

    You are welcome [:)]
    Madhivanan, Jul 2, 2008
    #7

Share This Page