0

I have SQL database that contains table of Articles that is related to newspaper table by np_id .

Article Table (id, title , date,newspaperID )
Newspaper Table ( newspaperID , name,logo)

I want to get the List of the News Paper including the total Number articles for each and the Latest article for each Newspaper, I want the result as below :

newspapre1 , Count of Total articles ( 4 articles ) , "I am the latest article", 1/25/2015
newspaper2,7 articles,"i am the latest in newspaper2",1/23/2015

what is the best practice to do such query ?

Tamer Hatoum
  • 49
  • 9
  • 1
    Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Jan 25 '16 at 19:27
  • what if 2 articles have the same date and are the most recent? How will you chose which is newer? – SQLChao Jan 25 '16 at 20:14

1 Answers1

0

In case the id of the Article is an increasing number and not an uniqueidentifier or some other custom made id you can use this query.

;WITH cte AS (
SELECT Newspaper.newspaperid, 
       COUNT(1) as TotalArticles, 
       MAX(Article.Id) AS LastArticleId
FROM Newspaper
INNER JOIN Article ON Article.newspaperid = Newspaper.newspaperID
GROUP BY Newspaper.newspaperid
)
SELECT * FROM Article 
INNER JOIN cte ON Article.Id = cte.LastArticleId

If the article id is not an increasing number the query gets more complex and you'll need one of the following

Retrieving the last record in each group

also take a look here Grouping and counting rows by value until it changes

If you use SQL Server 2012+ you may also find this read interesting https://msdn.microsoft.com/en-us/library/hh231517.aspx

Community
  • 1
  • 1
Mihail Shishkov
  • 14,129
  • 7
  • 48
  • 59