SQL: Select column in group by based on another column's aggrigate

Question

Say I have the table of the following format

UID  |  A  |  B
----------------
1       2     1
2       5     1
3       4     1
4       3     2
5       8     2
6       7     2
7       4     3
8       1     3
9       6     3

I want to select the UID that corresponds to the MAX(A) grouped by B

The following statement would not work because MAX(UID) is independent from MAX(A)

SELECT MAX(UID), MAX(A), B
FROM Table
GROUP BY B

The data I wanted returned would like like the following

UID  |  A  |  B
----------------
2       5     1
5       8     2
9       6     3

Answer 1

You need something like this:

SELECT t1.UID, t1.A, t1.B
FROM Table t1
INNER JOIN (
SELECT MAX(A) as A, B
FROM Table
GROUP BY B) T2 ON T1.A = T2.A AND T1.B = T2.B

Answer 2

There are a couple of ways you can do this, but the basic premise is the same. Find each B & MAX(A) combination and join this result set back to your source table on those columns to return the UID. This db<>fiddle highlights two possible, simple solutions.

The code for each:

/* Option #1 */
WITH MaxBs AS (
  SELECT B, MAX(A) AS A
  FROM TestData
  GROUP BY B
)

SELECT t.UID, t.A, t.B
FROM TestData t
INNER JOIN MaxBs b ON b.B = t.B and b.A = t.A
ORDER BY UID

/* Option #2 */

SELECT t.UID, t.A, t.B
FROM TestData t
INNER JOIN 
(
  SELECT MAX(A) as A, B
  FROM TestData
  GROUP BY B
) b ON t.A = b.A AND t.B = b.B

Both of these return your desired result set:

UID  |  A  |  B
---------------
2       5     1
5       8     2
9       6     3