Jim Jim - 1 month ago 18
SQL Question

MS Access SQL add non aggregate field to aggregate

I have the following table:

tableX

+----+----+----+
| f1 | f2 | f3 |
+----+----+----+
| 1 | a | 12 |
| 2 | a | 54 |
| 3 | b | 23 |
| 4 | b | 18 |
| 5 | b | 75 |
+----+----+----+


I'd like to reduce the table based on the highest value in f3 with respect to f2 such that we get:

+----+----+----+
| f1 | f2 | f3 |
+----+----+----+
| 2 | a | 54 |
| 5 | b | 75 |
+----+----+----+


I can reduce f2 and f3 by using:

SELECT f2, Max(f3)
FROM tableX
GROUP BY f2;


I think one solution is close to the below (using aliases and a self join) but I'm struggling a bit trying to get it to work - Access complains that f1 is not part of the aggregate.

SELECT a.f1,a.f2, Max(a.f3)
FROM tableX t1
Inner Join tableX t2
On t1.f1 = t2.f1
GROUP BY t1.f2;

Answer

You were on the right track.

Here is what I came up with:

select x.f1, x.f2, x.f3
from tablex x
join (
select f2, max(f3) as f3
from tableX
group by f2
  ) j on j.f2 = x.f2 and j.f3 = x.f3

I did a little SQL Fiddle for it. http://sqlfiddle.com/#!9/7d4ac4/3

It does not target ms-access, but it should work.