SQL Group By Question

[loyal ginger]

I am trying to do this in Microsoft Access. I seem to be unable to construct a 'simple' query. To illustrate, here is a sample table:

Field1 Field2 Amount
A New 10
D Old 5
G Old 1
H Old 15
H New 55
X Old 20
Z Old 100
Z New 20

I need to group by Field1 and only return the Field2 data for the record that has the maximum number in the Amount field. I tried to use the 'First' function but Microsoft Access sort the records internally so it does not return the right value even if I pre-sort the records with a sub-query. Do you have any advice? The result should look like the table below. Desired Result:

Field1 Field2 Max(Amount)
A New 10
D Old 5
G Old 1
H New 55
X Old 20
Z Old 100

I have trouble getting the value of Field2 in the record containing to the maximum value of 'Amount.' Any suggestions? Thanks in advance for your time!

[Blue_Boy]

You can try this query

SELECT mt.Field1,
(
SELECT TOP 1 mt2.Field2
FROM myTable mt2
WHERE mt2.Field1 = mt.Field1
ORDER BY
mt2.Amount DESC
) AS Field2,
MAX(mt.Amount)
FROM myTable mt
GROUP BY
mt.Field1

---

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

[loyal ginger]

Thanks for your time!