0

Given the following schema,

Article (issueID, articleID, author, title)
Citation (articleID, issueID, citedArticleID, citedIssueID)
WordAppears (wordID, issueID, articleID, position)
WordIs (wordID, wordText)
Issue (issueID, date, howManyDistributed),

I need to write a SQL statement to find the most-cited article(s) in the newspaper’s history, aka find the max number of instances of any particular citedArticleID.

How do chain max(), count(), and groupby (or any other methods, not restricted to these) to achieve this?

Kami
  • 19,134
  • 4
  • 51
  • 63
James. W. Phua
  • 1
  • 1
  • 3
    Sample data and desired results would be helpful -- as would your attempt to solve the problem. – Gordon Linoff Apr 02 '19 at 10:40
  • 2
    Welcome to Stack Overflow! You seem to be asking for someone to write some code for you. Stack Overflow is a question and answer site, not a code-writing service. Please [see here](http://stackoverflow.com/help/how-to-ask) to learn how to write effective questions. – Esteban P. Apr 02 '19 at 10:40
  • 3
    This sounds like a homework question - what have you tried so far? https://stackoverflow.com/questions/8387587/selecting-a-record-with-max-value – Kami Apr 02 '19 at 10:42
  • Thanks guys, I think I found what I was looking for here: https://stackoverflow.com/questions/6919880/writing-sql-query-for-getting-maximum-occurrence-of-a-value-in-a-column – James. W. Phua Apr 02 '19 at 11:13

1 Answers1

0

A top 10 of the article titles with the most citations :

SELECT TOP 10 
 a.articleID,
 a.title AS ArticleTitle
FROM Article a 
JOIN Citation c ON c.citedArticleID = a.articleID
GROUP BY a.articleID, a.title
ORDER BY COUNT(*) DESC;

The order puts those with the highest total on top.
And the top 10 only shows the first 10 from that result.

LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • a.articleID should be part of the select statement so that duplicate titles ( aka "president says hes best") can be distinguished – Mathias F Apr 02 '19 at 11:33
  • @MathiasF Well, you could put a.articleID in the select. But it's used in the group by, so for the totals it'll be ok. Two articles with the same title would have a different articleID, and thus different counts. But ok, I'll add it just in case. – LukStorms Apr 02 '19 at 11:39