GROUP BY one field, keeping other fields

Question

Base table bod with three fields:

| BOFORM_ID | FEINBOD | MAECHTBOD | 
+-----------+---------+-----------+
|   3301211 | fSms    |        50 |
|   3301211 | mSfs    |       150 |
|   3301231 | fSms    |       200 |
|   3301312 | Ss      |       150 |
|   3301312 | Su2     |        50 |

I'd like to

Group by BOFORM_ID
get the maximum value from MAECHTBOD
while keeping the associated FEINBOD from the same row as the maximum of MAECHTBOD.

I don't know how to implement step 3.

My desired output:

| BOFORM_ID | FEINBOD | MAECHTBOD | 
+-----------+---------+-----------+
|   3301211 | mSfs    |       150 |
|   3301231 | fSms    |       200 |
|   3301312 | Ss      |       150 |

Look up windowing functions (particularly PARTITION BY)... https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017 — Joe Phillips, Dec 10 '18 at 17:41
@JoePhillips ... MS Access is a different Microsoft product and uses the JET/ACE SQL dialect not T-SQL. ACE does not support window functions. — Parfait, Dec 10 '18 at 18:03

score 2 · Answer 1 · edited Dec 10 '18 at 18:04

2

You can use correlated subquery :

SELECT b.*
FROM bod as b
WHERE MAECHTBOD = (SELECT MAX(b1.MAECHTBOD) FROM bod as b1 WHERE b1.BOFORM_ID = b.BOFORM_ID);

edited Dec 10 '18 at 18:04

Gordon Linoff

1,242,037
58
646
786

answered Dec 10 '18 at 17:43

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 2 · answered Dec 10 '18 at 18:07

Consider joining your unit level data to aggregate query. This approach would be more efficient than correlated subquery which calculates the aggregate for each row of outer query and not once as below.

SELECT t.BOFORM_ID, t.FEINBOD, t.MAECHTBOD 
FROM myTable t
INNER JOIN (
   SELECT sub_t.BOFORM_ID, MAX(sub_t.MAECHTBOD) AS MAX_BOD
   FROM myTable sub_t
   GROUP BY sub_t.BOFORM_ID
) AS agg
ON t.BOFORM_ID = agg.BOFORM_ID AND t.MAECHTBOD = agg.MAX_BOD

GROUP BY one field, keeping other fields

2 Answers2