Finding Max values in groups with a query

[JHSam]

I'm having some problems with a query to do the following: Pick the highest value for Field "X" and its' corresponding value in Field "Y", from a group of values in Field "X" with a given characteristic. An example follows:

Field1 FieldX FieldY
Blue 23 9
Blue 16 10
Red 12 3
Red 6 11
Red 3 8

So for Blue, it returns a value of 23 (for Field X) and 9 (for Field Y). For Red, it returns a value of 12 (for Field X) and 3 for Field Y.

I thought that I could run this using the group function, but it doesn't seem to be working.

Any suggestions would be appreciated.

JHSam

 

[Corticus]

Hi JHSam,

Try SQL as follows:

SELECT TOP 1 Tbl1.Field1, Tbl1.FieldX, Tbl1.FieldY
FROM Tbl1
WHERE (((Tbl1.Field1)=[Enter Color]))
ORDER BY Tbl1.FieldX DESC;

To use this SQL, select "SQL" from that icon that lets you choose your query view (design/datasheet/SQL).

Paste in the above, replacing whatever might be there.

You will probably have to adjust Tbl1 to your table and likewise with the field names.

HTH,

 

[JHSam]

Thanks -- that worked if I wanted to see the output for each value in Field1 (i.e., colors, in my example) individually. I was looking more for the query output the results for all of the colors in one pass -- my apologies for being unclear on this in my original message.

I thought the domain aggregate functions might work, but these rolled together all of the records as opposed to the records for each value in Field1.

 

[Russell Hauf]

Try this. My table is named tbl060303, so change all instances of this to your table name. My fields are as you used in your example.

SELECT A.Field1, A.MaxFieldX, B.FieldY FROM
(SELECT Field1, Max(FieldX) AS MaxFieldX FROM tbl060303 GROUP BY Field1) AS A
INNER JOIN (SELECT * FROM tbl060303) AS B ON A.Field1=B.Field1 and A.MaxFieldX=B.FieldX;

HTH,

Russell

 

[JHSam]

Thanks for the help. Before I viewed your posting, I had figured out a way to do it with a combination of a crosstab query and select query. This gave comparable results to that from your posting.

 

[Russell Hauf]

Yep, multiple queries is always a good route (but you shouldn't need a crosstab).

And I hope the results were not only comparable, but exactly the same!

-rh