Simple Question - I am sure 
Author Message
 Simple Question - I am sure

Assume the following data:

REFNUM  NAME AMOUNT
1                Joe           37
2                Tom          54
1                Bill            42
1                Sue           23
2                Sue            27

I want a sql statement to return the person who has the highest amount for
each refnum. How do I do that?

So the answer would be:

REFNUM  NAME AMOUNT
1                Bill            42
2                Tom          54

Thanks,
Craig



Fri, 17 Mar 2006 17:00:40 GMT
 Simple Question - I am sure

Try:

select a.*
from table1 a join
(select refnum, max(amount) amt from table1 group by
refnum) b
on a.refnum = b.refnum and a.amount = b.amt

- Vishal

Quote:
>-----Original Message-----
>Assume the following data:

>REFNUM  NAME AMOUNT
>1                Joe           37
>2                Tom          54
>1                Bill            42
>1                Sue           23
>2                Sue            27

>I want a sql statement to return the person who has the
highest amount for
>each refnum. How do I do that?

>So the answer would be:

>REFNUM  NAME AMOUNT
>1                Bill            42
>2                Tom          54

>Thanks,
>Craig

>.



Fri, 17 Mar 2006 17:21:33 GMT
 Simple Question - I am sure
I have found the answer:

SELECT     a.refnum, a.name, a.totamount
FROM       tablename a
WHERE      a.totamount = (SELECT max(b.amount)
           FROM tablename b
           WHERE b.refnum = a.refnum)
ORDER BY   a.refnum


Quote:
> Assume the following data:

> REFNUM  NAME AMOUNT
> 1                Joe           37
> 2                Tom          54
> 1                Bill            42
> 1                Sue           23
> 2                Sue            27

> I want a sql statement to return the person who has the highest amount for
> each refnum. How do I do that?

> So the answer would be:

> REFNUM  NAME AMOUNT
> 1                Bill            42
> 2                Tom          54

> Thanks,
> Craig



Sun, 19 Mar 2006 13:46:32 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. New to SQL, easy question I am sure

2. A Simple Question I'm sure

3. Embarassingly simple SQL question - what am I missing?!

4. Simple question..I am running a stored procedure from SQL PLUS

5. Simple question..I am running a stored procedure from SQL PLUS

6. I am sure this is pretty basic...but

7. I am sure this has been answered before

8. I'm sure it something simple but it's driving me nuts :-(

9. Simple error-I'm sure

10. SQL7, SIMPLE SIMPLE SIMPLE question

11. PL/SQL Calculated Fields - Simple I'm sure


 
Powered by phpBB® Forum Software