max

[atthevilla]

This is the query I'm working on:

SELECT ID, MAX(AMOUNT)
FROM GIFTS
GROUP BY ID

This gives me the correct amount but I also need the
year associated with the largest amount for each ID.

The query below which includes the year will give me only the maximum per ID per year. Is there a way to get the
correct maximum while showing the year of that particular
gift?

SELECT ID, MAX(AMOUNT), YR
FROM GIFTS
GROUP BY ID, YR

 

[SantaMufasa]

Boylston,

First, do you pahk yuh cah in Hahvud Yahd? (Just kidding.)

Second, here are some sample data:

select * from gifts;

   ID     AMOUNT         YR
----- ---------- ----------
    1       1000       2003
    1       2000       2002
    1       3000       2005
   17      10000       2001
   17      20000       2004
   17      40000       2006

Lastly, here is some code and results that should do what you want:

select b.id, top, b.yr
  from (SELECT  ID, MAX(AMOUNT) to
          FROM GIFTS 
         GROUP BY ID) a
      ,gifts b
 where b.amount = top
   and b.id = a.id
/

   ID        TOP         YR
----- ---------- ----------
    1       3000       2005
   17      40000       2006

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[atthevilla]

Thanks for the try. I'm still getting multiple IDs for the same person when there is more than one max amount for the in different years, for example

ID AMOUNT YR
----- ---------- ----------
1 1000 2003
1 2000 2002
1 3000 2004
1 3000 2005
17 10000 2001
17 10000 2003
17 20000 2004
17 40000 2006

I'd want to see one row with the year of the most recent max when there is more than one

ID AMOUNT YR
----- ---------- ----------
1 3000 2005
17 10000 2003

 

[carp]

Boylston -
Try this:

SELECT t.id, t.amount highest_gift, max(year) latest_year 
FROM mytable t,
    (SELECT id, max(amount) highest_donation 
     FROM mytable group by id) v
WHERE t.amount = v.highest_donation 
  AND t.id = v.id
GROUP BY t.id, t.amount;

ID HIGHEST_GIFT LATEST_YEAR
---------- ------------ -----------
1 3000 2005
17 40000 2006

 

[SantaMufasa]

It's always nice to have all the specs up front. Nice enhancement, Dave (Carp)!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[carp]

As usual, as soon as I posted the above I thought of a more efficient approach. The problem with the above query is that it requires two full table scans of mytable and multiple sorts. If you only have 8 rows, this is not a killer; if you have 8 million rows, it's a bit more painful. So you might want to try the following:

SELECT
   id, 
   substr(a_y,1,
          instr(a_y,'.')-1) amount,
   substr(a_y,
          instr(a_y,'.')+1,4) year
FROM (SELECT id, 
             max(amount||'.'||year) a_y
        FROM mytable
      GROUP BY id);

Here are the execution plans/statistics for your consideration:

For the first query:

Execution Plan
---------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     MERGE JOIN
   3    2       SORT (JOIN)
   4    3         VIEW
   5    4           SORT (GROUP BY)
   6    5             TABLE ACCESS (FULL) OF 'MYTABLE'
   7    2       SORT (JOIN)
   8    7         TABLE ACCESS (FULL) OF 'MYTABLE'




Statistics
---------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          2  rows processed

For the second query:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     TABLE ACCESS (FULL) OF 'MYTABLE'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        506  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

Take your pick!

 

[carp]

And NOW I see I STILL haven't provided a solution.
Boylston - what is it you really want? The most recent year in which somebody gave as much as they did in another year (as indicated by your sample data)? The most recent year in which they gave the most (provided by the above queries)? The highest donor for the year? ?????

 

[atthevilla]

Thank you all for the help. That works nicely.