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
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.