CODEWITHSUNDEEP
databasems-access
akinboj
akinboj

Reputation: 371

Get Maximum Value

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;

How can I fix this.. Thanks

Upvotes: 0

Views: 109

Answers (4)

Ravi Pal
Ravi Pal

Reputation: 395

Select ProposalID, Title, RequestedAmount AS Bugdet, ResearcherID
From Proposal
WHERE RequestedAmount = (SELECT MAX(RequestedAmount) FROM Proposal);

You can try this One...

Upvotes: 0

Nithin Paul
Nithin Paul

Reputation: 2199

Can you try this one

SELECT TOP 1 Proposal.ProposalID, Proposal.Title, (Select MAX(Proposal.RequestedAmount) from Proposal) AS Budget, Proposal.ResearcherID FROM Proposal;

Hope this helps....

Upvotes: 1

Patashu
Patashu

Reputation: 21773

In an aggregate function using queries, all selected values must either be

1) in the group by

2) an aggregate function.

This is because max is not the only aggregate function - imagine if you used AVG instead of MAX. What values would SQL have to give for the other rows, that apply only to one row? There's no way to pick, which is why you cannot express such a query.

But what you want is not to aggregate but to FIND the row with the highest RequestedAmount. So let's do:

Select ProposalID, Title, RequestedAmount AS Budget, ResearcherID
From
Proposal
Where Budget >= ALL (select RequestedAmount from Proposal)

Upvotes: 0

Abdullah Shoaib
Abdullah Shoaib

Reputation: 2095

Use GROUP BY 

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

This is because you have listed multiple columns in your SQL SELECT statement that are not encapsulated in the MAX function this is why you must use the SQL GROUP BY clause.

Upvotes: 0

Related Questions