CODEWITHSUNDEEP
sqlsql-server-2008greatest-n-per-group
PassionateDeveloper
PassionateDeveloper

Reputation: 15138

Very hard greatest n per group query

I have a very complexe query here, I try to give you an overview about the necessary tables here:

We have X Chars per RPG, x Posts per Char. 1 User can have X Chars, but 1 Char only depens on 1 User.

What I want is a query in which I got the last post per RPG within information about the Username who wrote this, the character and the RPG itself addition to a number how much RPGPosts per RPG we have (total).

This is how far I solved it until now:

  SELECT        c.RPGID, c.Name, DateTime, r.Name, u.Username, t.count
  FROM            dbo.RPGCharacter c  inner join
   (
        SELECT  CharacterID,  
                MAX(DateTime) MaxDate
        FROM    RPGPost
        GROUP BY CharacterID
    ) MaxDates ON c.RPGCharacterID = MaxDates.CharacterID 
    INNER JOIN  RPGPost p ON   MaxDates.CharacterID = p.CharacterID
                AND MaxDates.MaxDate = p.DateTime
    Inner join RPG r on c.RPGID = r.RPGID
    Inner join [User] u on u.UserID = c.OwnerID
    inner join (Select RPG.RPGID, Count(*) as Count from RPGPost 
                inner join RPGCharacter on RPGPost.CharacterID = RPGCharacter.RPGCharacterID
                inner join RPG on RPG.RPGID = RPGCharacter.RPGID 
                where RPGPost.IsDeleted = 0
                Group by RPG.RPGID) t on r.RPGID = t.RPGID
    Order by DateTime desc

Result : http://abload.de/image.php?img=16iudw.jpg

This query gives me all I want but has an Errors:

1) It gives me the last post per Character, but I need the last Post per RPG

Upvotes: 0

Views: 172

Answers (1)

Jodrell
Jodrell

Reputation: 35716

Does this help? This should give you the last post per CharacterID in the RPGPost table and include the total number of posts for that CharacterID.

WITH RankedPost AS (
    SELECT
        P.PostID,
        P.CharacterID,
        P.DateTime
        RANK() OVER (
                PARTITION BY CharacterID, 
                ORDER BY DateTime DESC) Rank,
        RANK() OVER (
                PARTITION BY CharacterID, 
                ORDER BY DateTime ASC) Count
        FROM RPGPost P)
SELECT
    P.DateTime
    P.CharacterID,
    P.Count
FROM RankedPost P
WHERE
    RankedPost.Rank = 0;

Upvotes: 1

Related Questions