0

I need to get the ID based from what ever the max amount is. Below is giving me an error 

   select  ID from Prog 
   where Amount = MAX(Amount)

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

The end result is that I need to get the just the ID as I need to pass it something else that is expecting it.

Nate Pet
  • 44,246
  • 124
  • 269
  • 414

5 Answers5

3

You need to order by Amount and select 1 record instead...

SELECT ID
FROM Prog
ORDER BY Amount DESC
LIMIT 1;

This takes all the rows in Prog, orders them in descending order by Amount (in other words, the first sorted row has the highest Amount), then limits the query to select only one row (the one with the highest Amount).

Also, subqueries are bad for performance. This code runs on a table with 200k records in half the time as the subquery versions.

kitti
  • 14,663
  • 31
  • 49
  • Your solution is the same solution that got one upvote, just in standard SQL and you got a downvote, i don't really understand why ... I think that someone here press down vote without any reason ... :) – aleroot Mar 06 '12 at 19:48
2

If you're using SQL Server that should do it :

SELECT TOP 1 ID 
FROM Prog
ORDER BY Amount DESC
Fid
  • 568
  • 5
  • 13
2

Just pass a subquery with the max value to the where clause : 

   select  ID from Prog 
   where Amount = (SELECT MAX(Amount) from Prog)
aleroot
  • 71,077
  • 30
  • 176
  • 213
  • 1
    Sometimes i really can't understand why some correct answers get downvoted ... Could someone explain whats wrong with my solution ? Thanks – aleroot Mar 06 '12 at 19:45
  • I don't know, I think some a** was coming through downvoting every answer... back up to 0 now though! :D – kitti Mar 06 '12 at 19:47
  • Maybe today is the downvote day, and we didn't noticed that ... :-) – aleroot Mar 06 '12 at 19:49
  • It's backwards day! Downvotes are upvotes and upvotes are downvotes! – kitti Mar 06 '12 at 19:50
  • Oh and this will also select more than one row if they have the same `Amount` value. – kitti Mar 06 '12 at 19:56
1

This should be something like:

select P.ID from Prog P
where P.Amount = (select max(Amount) from Prog)

EDIT:

If you really want only 1 row, you should do:

select max(P.ID) from Prog P
where P.Amount = (select max(Amount) from Prog);

However, if you have multiple rows that would match amount and you only want 1 row, you should have some kind of logic behind how you pick your one row. Not just relying on this max trick, or limit 1 type logic.

Also, I don't write limit 1, because this is not ANSI sql -- it works in mysql but OP doesn't say what he wants. Every db is different -- see here: Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? Don't get used to one db's extensions unless you only want to work in 1 db for the rest of your life.

Community
  • 1
  • 1
Mike Ryan
  • 4,234
  • 1
  • 19
  • 22
  • This will select more than one row if they have the same `Amount` value. – kitti Mar 06 '12 at 19:55
  • True -- typed fast. Everyone's using `limit 1` or `max` or something like that. But unless he wants essentially random results, questioner should be using some kind of business logic to pick the 1 row. – Mike Ryan Mar 06 '12 at 21:06
-1
select min(ID) from Prog 
where Amount in 
(
    select max(amount)
    from prog
)

The min statement ensures that you get only one result.

phlogratos
  • 13,234
  • 1
  • 32
  • 37
  • Using `LIMIT 1` also ensures you only get one result, and doesn't require any explanation. – kitti Mar 06 '12 at 19:48