Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using MS Access database. I have a table (Proposal) with the following properties: ProposalID - PK, Title, RequestedAmount, ResearcherID - FK

I want to run an SQL query to get the candidate that has requested for the highest amount. My query that didn't work is:

Select ProposalID, Title, Max(RequestedAmount) AS Budget, ResearcherID
From
Proposal;

I get this error:

You tried to execute a query that does not include the specified expression 'ProposalID' as part of an aggregate function

How can I fix this?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

If you want to use aggregated fields with not aggregated fields you will have to use Group by with the not aggregated fields. But in your case IMHO you are searching for:

Select ProposalID, Title, RequestedAmount AS Budget, ResearcherID
From
Proposal
where RequestedAmount=(Select Max(RequestedAmount) from Proposal)
share|improve this answer
    
what about ties? –  Mitch Wheat May 10 '13 at 7:24
    
@MitchWheat I know Tie from SQL-Server, do you know from MS-Access? –  bummi May 10 '13 at 9:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.