|
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!
modified 6-Dec-12 11:51am.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
