I need to query an SQL database to find all records with max RATE for each combination of fields A and B. For example, consider the following table with 4 columns, A, B, RATE & VALUE:
A B RATE VALUE
= = ==== =====
a b 10 100
a b 2 110
a c 1 20
a c 3 55
a d 8 22
b c 3 10
I wish to get back one row from each distinct pair A & B with max RATE, getting these rows:
A B RATE VALUE
= = ==== =====
a b 10 100
a c 3 55
a d 8 22
b c 3 10
How can I formulate such a query in SQL?