I want to get the subscriber that has maximum value of Bill (Total Bill). I tried using the following script but SQL did not execute successflly. Please help me on what I did wrong on this. I have 2 tables:
Subscriber
| FirstName | MIN |
|---|---|
| Ben | 258999542 |
| Reed | 458524896 |
| Steve | 586692155 |
| Clint | 1007772121 |
| Frank | 1287548752 |
| Jane | 2345824215 |
Total Bill
| Total | MIN |
|---|---|
| 131.5 | 258999542 |
| 139.4 | 458524896 |
| 164 | 586692155 |
| 101 | 1007772121 |
| 224.12 | 1287548752 |
| 97.52 | 2345824215 |
And here’s the code I tried:
SELECT MAX(B.Total), S.FirstName FROM Subscriber AS S JOIN Bill AS B ON S.MIN = B.MIN
Advertisement
Answer
It seems you just need TOP + ORDER BY:
SELECT TOP 1 B.Total, S.FirstName FROM Subscriber AS S JOIN Bill AS B ON S.MIN = B.MIN ORDER BY B.Total DESC;
That’s based on the fact that your sample data isn’t showing multiple Bill records per Subscriber therefore you don’t need a sum.