MAX sql query in oracle

Question

I have the testmax table as following :

I                      J                      
---------------------- ---------------------- 
1                      2                      
2                      4                      
3                      3

Now, the problem is how can I find the I which has max J, by following I can find only what is the max J

SELECT MAX(j) 
  FROM testmax

but by following I get this error: ORA-00937: not a single-group group function:

SELECT i, MAX(j) 
  FROM testmax

I just played few times with oracle xe but you may take a look at rownum. — Nicola Cossu
– Nicola Cossu, Commented Jun 3, 2011 at 23:19

Rob van Wijk · Accepted Answer · 2011-06-04 19:10:39Z

Note that your question is still somewhat ambiguous; what should be returned when there is more than one record with a maximum value for J. Will you return one record or more than one? My answer is only applicable if you want one record returned.

And in that case, the query below, using FIRST/LAST aggregate function for i, is the most efficient query.

A small test with your table:

SQL> create table testmax (i,j)
  2  as
  3  select 1, 2 from dual union all
  4  select 2, 4 from dual union all
  5  select 3, 3 from dual
  6  /

Table created.

And the query using the LAST aggregate function (http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions076.htm#sthref1540):

SQL> set autotrace on explain
SQL> select max(i) keep (dense_rank last order by j) i
  2       , max(j)
  3    from testmax
  4  /

         I     MAX(J)
---------- ----------
         2          4

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 44308443

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |    26 |            |          |
|   2 |   TABLE ACCESS FULL| TESTMAX |     3 |    78 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Also with one table scan, but using an analytic function on all the rows, where a single aggregate will do just fine:

SQL> select i,j
  2    from (
  3      select i, j, max(j) over () max_j
  4        from testmax
  5    )
  6    where j=max_j
  7  /

         I          J
---------- ----------
         2          4

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1897951616

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     3 |   117 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |         |     3 |   117 |     3   (0)| 00:00:01 |
|   2 |   WINDOW BUFFER     |         |     3 |    78 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TESTMAX |     3 |    78 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("J"="MAX_J")

Note
-----
   - dynamic sampling used for this statement (level=2)

This one uses two table scans instead of one:

SQL> SELECT i, j
  2    FROM testmax
  3   WHERE j = ( SELECT MAX(j) from testmax )
  4  /

         I          J
---------- ----------
         2          4

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3795151209

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |    26 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL  | TESTMAX |     1 |    26 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |         |     1 |    13 |            |          |
|   3 |    TABLE ACCESS FULL| TESTMAX |     3 |    39 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("J"= (SELECT MAX("J") FROM "TESTMAX" "TESTMAX"))

Note
-----
   - dynamic sampling used for this statement (level=2)

Regards,
Rob.

eaolson · Accepted Answer · 2011-06-03 23:39:59Z

3

SELECT i, j
  FROM testmax
 WHERE j = ( SELECT MAX(j) from testmax )

This may return more than one row if j is not unique.

answered Jun 3, 2011 at 23:39

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

Comments

Andrew Lazarus · Accepted Answer · 2011-06-03 23:26:36Z

1

I think you can simplify the first query, and then we can use order by to clean up.

SELECT year, COUNT(*) AS c
FROM 
Movie M INNER JOIN ActedIn A ON M.movieID=A.MovieID
WHERE A.actorID=518238628
GROUP BY year
ORDER BY COUNT(*) DESC LIMIT 1;

Your RDBMS may use SELECT TOP 1 instead of LIMIT 1.

answered Jun 3, 2011 at 23:26

Andrew Lazarus

19.5k3 gold badges39 silver badges57 bronze badges

2 Comments

APC Over a year ago

Not sure what's happened but this looks like the answer to a different question.

Andrew Lazarus Over a year ago

@APC, yeah, the question has changed.

Dave Costa · Accepted Answer · 2011-06-05 14:24:02Z

1

This may be more efficient than using a subquery.

select i,j
  from (
    select i, j, max(j) over () max_j
      from testmax
  )
  where j=max_j
  ;

edited Jun 5, 2011 at 14:24

answered Jun 4, 2011 at 13:35

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

2 Comments

Rob van Wijk Over a year ago

ERROR at line 4: ORA-00923: FROM keyword not found where expected

Dave Costa Over a year ago

@Rob, oops, quite right. Added the FROM clause on the inner query.

Jose Rui Santos · Accepted Answer · 2011-06-06 09:11:15Z

0

It is more correct to use the having clause when you need to query aggregate functions

select i, max(j)
  from testmax
 group by i
having max(j) = (select max(j) from testmax);

I                      MAX(J)                 
---------------------- ---------------------- 
2                      4                      

1 rows selected

answered Jun 6, 2011 at 9:11

Jose Rui Santos

15.3k9 gold badges61 silver badges74 bronze badges

Collectives™ on Stack Overflow

MAX sql query in oracle

5 Answers 5

Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related