I want to SELECT MAX wickets with the NAME

SELECT NAME, MAX(WICKETS) FROM BOWLER;

but get this error not a single-group group function

what should I do;

CodePudding user response：

Here's sample table:

SQL> select ename, sal from emp order by sal desc;

ENAME             SAL
---------- ----------
KING             5750       --> I presume you want KING, as his salary
FORD             3450           is greatest (i.e. MAX) in that data set
SCOTT            3450
JONES            3421
<snip>

If that's so, then

SQL> select a.ename, a.sal
  2  from emp a
  3  where a.sal = (select max(b.sal) from emp b);

ENAME             SAL
---------- ----------
KING             5750

SQL>

Query you posted presumes that there are several rows per each name, and you want to get MAX value of the wickets column per each name without group by. Why would you want that? GROUP BY is what you should be using, then.

CodePudding user response：

Use an analytic function:

SELECT name,
       MAX(wickets) OVER () AS global_maximum,
       MAX(wickets) OVER (PARTITION BY name) AS name_maximum
FROM   bowler;

Which, for the sample data:

CREATE TABLE bowler (name, match_id, wickets) AS
SELECT 'Alice', 1, 0 FROM DUAL UNION ALL
SELECT 'Alice', 2, 3 FROM DUAL UNION ALL
SELECT 'Alice', 3, 4 FROM DUAL UNION ALL
SELECT 'Beryl', 1, 2 FROM DUAL UNION ALL
SELECT 'Beryl', 2, 1 FROM DUAL UNION ALL
SELECT 'Beryl', 3, 0 FROM DUAL;

Outputs:

NAME	GLOBAL_MAXIMUM	NAME_MAXIMUM
Alice	4	4
Alice	4	4
Alice	4	4
Beryl	4	2
Beryl	4	2
Beryl	4	2

If you want to filter to only get the row that is a maximum for the name then:

SELECT name,
       match_id,
       global_maximum,
       name_maximum
FROM   (
  SELECT name,
         match_id,
         wickets,
         MAX(wickets) OVER () AS global_maximum,
         MAX(wickets) OVER (PARTITION BY name) AS name_maximum
  FROM   bowler
)
WHERE wickets = name_maximum;

Which outputs:

NAME	MATCH_ID	GLOBAL_MAXIMUM	NAME_MAXIMUM
Alice	3	4	4
Beryl	1	4	2

db<>fiddle here