Select an information from a row based on the maximum value of specific column in a group by?

Question

I have this data


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Id| Date_Opera | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     Value 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1, | 2017-07-07 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0002,        121.72
2, | 2017-09-27 | Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
3, | 2017-10-17 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0048,        51.81
4, | 2017-05-01 | Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
5, | 2017-11-17 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0001,        133.99
6, | 2017-10-10 | Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30
7, | 2017-07-18 | Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0004,        186.99

And i would like to get a result like this one below

Calculate the number of operation that a couple of EmitterIBAN and ReceiverIBAN made
Calculate the sum values for each couple of EmitterIBAN and ReceiverIBAN
And Group by the addresses that can be different by taking the maximum address value


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sum| Date_Opera | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     SumValue 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4, | 2017-11-17 |  Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point           0048,      494,51
1, | 2017-09-27 |  Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
1, | 2017-05-01 |  Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
1, | 2017-10-10 |  Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30

So to get this result i use this request

Select  count(1) as NumberOperation, 
        MAX(Emitter) as EmitterName, 
        EmitterIban, 
        MAX(Receiver) as ReceiverName, 
        ReceiverIban,
        MAX(ReceiverAddress) as ReceiverAddress,
        SUM([Value]) as SumValues
FROM TableEsperadoceTransaction
Group By EmitterIban,
         ReceiverIban

but now, what i want is, instead of taking the maximum address just like the example before, I would like to take the address, from the record that has the biggest data time operation. Here's an exemple of my data result how shoud look like


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sum| Date_Opera | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     SumValue 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4, | 2017-11-17 |  Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point           0002,      494,51
1, | 2017-09-27 |  Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
1, | 2017-05-01 |  Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
1, | 2017-10-10 |  Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30

So my question is how can i do such request?

PS: I have 240 million records

Edit : I have 3 Index

Date_Operation
EmitterIban
ReceiverIban

Select an information from a row based on the maximum value of specific column in a group by?

3 Answers