I need a query that retrieves this result

Question

I have a table of 3 columns:

• AuthorID (id can be repeated)
• JournalName (name can be repeated)
• AuthorScore

I need a query that gets JournalName and the count of all authors having their maximum score in this journal.

Thank you in advance.

Answer 1

SELECT AuthorID, MAX(AuthorScore) as AuthorScore, 
    (
        SELECT JournalName 
        FROM tab t2 
        WHERE t1.AuthorID = t2.AuthorID AND t2.AuthorScore = MAX(t1.AuthorScore)
    ) as JournalName
FROM tab t1
GROUP BY AuthorID

Answer 2

select 
    maxscorejournalinstances.journalname,
    COUNT(*) as maxscorecount
from
    (
    select 
        journalname
        from
    foo inner join
        (
            select 
                authorid,
                MAX(authorscore) as maxscore
            from
                foo
            group by
                authorid
        ) maxauthorscores
    on foo.AuthorId = maxauthorscores.AuthorId 
    and foo.AuthorScore = maxauthorscores.maxscore
    ) maxscorejournalinstances
group by
    maxscorejournalinstances.JournalName

Note that if an author has the same high score in two or more journals, each of those journals will be included in the resultset.

Answer 3

select 
   x.journalname, count(x.authorid)
from tableX x
inner join

(
select authorid, max(authorscore) max_authorscore
from tableX
group by authorid
) tmp on x.authorid=tmp.authorid and x.authorscore=tmp.max_authorscore
group by journalname

Answer 4

Something like this may work.

select authorid, journalname, authorscore, max(authorscore) over(parition by authorid)
from <table>
order by journalname

Doing some research on sql olap function should point you in the right direction if this doesn't work.

Answer 5

Sounds like you need 2 queries as the data returned can not be returned in one record set where the data layout is simple to see.

The count of authors per journal,

select JournalName, count(distinct AuthorID) 
from table
group by JournalName

The author's max score per journal,

select JournalName, AuthorID, max(AuthorScore)
from table
group by JournalName, AuthorID