-1

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
  • 1
    You need a subselect. – mustaccio Nov 7 at 20:56
  • Agreed with @mustaccio. Another way is to go with partitioned row number, but note thay you couldn’t use it in Where, so basically 2 Select at least. – Dat Nguyen Nov 8 at 1:57
0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.