By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

How to select other data columns when using max function?

100+
P: 167
I am trying to run a query in access using the 'max' function. For example, I have many rows of data, one for each state and each state has a column for 'Miles'. I want to get the highest number of miles so I am using the max function... Select max(miles) from table. It works and returns the largest number in this column. I also need the state that is associated with the max miles so I changed my select to:
Select state, max(miles) from table. However, when I run this select I get an error saying that 'state' is not part of an aggregate function. If I use any aggregate function on 'state', I do not get the correct info. How do I get other data columns from the same row the max function returns???
Oct 16 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,316
You have to tell it to group by state.
Expand|Select|Wrap|Line Numbers
  1. SELECT State, Max(Miles) AS MaxOfMiles
  2. FROM SomeTable
  3. GROUP BY State;
  4.  
Oct 16 '07 #2

100+
P: 167
This did not give me the results I needed. My data, for example has multiple rows for each of the states. Example:
St Miles
AL 100
AL 200
AL 300
WV 250
WV 350
WV 450
When I put the group by state on the select statement, it returned a row for each state with the max miles for that state so what I got was AL-300 miles, WV-450 miles, and etc. What I need is the max miles over all the rows of data, regardless of the state and whatever row it selects for the max miles, I need to know the state. Does this make sense?
Oct 17 '07 #3

Rabbit
Expert Mod 10K+
P: 12,316
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 State, Miles
  2. FROM SomeTable
  3. ORDER BY Miles DESC;
  4.  
This will return all records that tie for highest miles though.
Oct 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.