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

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