CODEWITHSUNDEEP
sqloracle-databasemaxgreatest-n-per-group
sKyTzi
sKyTzi

Reputation: 91

SQL ORACLE - Selecting author with most publications (most rows) using MAX()

I have problem with Oracle SQL script. I need to select all the authors (logins) with most publications. If there are more logins with the same number of publications, I need to select them all. list of publications looks like this:

I need to use MAX() function.. so far I have tried doing something like this:

SELECT P.LOGIN, COUNT(*)
FROM PISE P
GROUP BY P.LOGIN, HAVING COUNT(*) >= MAX(PUBLICATIONS)  
(
SELECT COUNT(*) AS PUBLICATIONS
FROM PISE P
GROUP BY P.LOGIN
);

resulting in ORA-00933: SQL command not properly ended

or

SELECT P.LOGIN, COUNT(*) as PUBLICATIONS
FROM PISE P
GROUP BY P.LOGIN HAVING PUBLICATIONS >= MAX(PUBLICATIONS);

resulting in ORA-00904: "PUBLICATIONS": invalid identifier

or

 SELECT P.LOGIN, COUNT(*)
    FROM PISE P
    WHERE COUNT(*) IN (
    SELECT MAX(COUNT(*)) 
    FROM PISE
    );

resulting in ORA-00934: group function is not allowed here.

This is the result I am looking for

(without Jmeno and Prijmeni column).

Upvotes: 0

Views: 269

Answers (1)

user5683823
user5683823

Reputation:

Using "with clause" (available in versions 11 and above):

with a as (select login, count(*) as cnt from p group by login),
     b as (select max(cnt) as max_cnt from a)
select a.login, a.cnt from a, b where a.cnt = b.max_cnt 
/

Upvotes: 1

Related Questions