Use of max in groupings

[mcgrcoAgain]

Firstly, i am a sql newbie so apologies if this is an obvious answer

I need to joing a table to another but unfortunatly theres no one to one relationship. What I would like to do on the table with mutiple records is is group by the key and sum by amount . I then want the query to select the max amount by record within the group and show me the details of the max amount. Is there a tecnique I can use that’s not to complicated. Any help is appreciated

E.g


Key Amount text
1 10 Wrong text 1
1 5 Wrong text 2
1 1000.26 RightText

The query should return

groupedKey SumAmount MaxAmount Maxtext
1 1015.26 1000.26 Right Text T

 

[WayneRyan]

M,

Make View1:

Select Key, Sum(Amount) As SumAmount From YourTable Group By Key

Make View2:

Select Key, Max(Amount) As MaxAmount From YourTable Group By Key

Make a query:

Select View1.Key, View1.SumAmount, View2.MaxAmount, YourTable.Text
From   YourTable Inner Join View1 On
         YourTable.Key = View1.Key Inner Join View2 On
            YourTable.Key = View2.Key
Where  YourTable.Amount = View2.MaxAmount

Or, just mash something like this together:

Select Distinct(A.Key) As Key, 
       (Select Sum(Amount) From YourTable Where Key = A.Key) As Amount,
       (Select Amount From YourTable Where Key = A.Key) As DetailAmount,
       (Select Text From YourTable Where Key = A.Key) As DetailText
From   YourTable As A
Order By A.Key

Wayne