MAX sql query in oracle

I have the testmax table as following :

I开发者_StackOverflow                      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

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.

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

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

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.

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
  ;

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