CODEWITHSUNDEEP
sqlsql-server
Craig B
Craig B

Reputation: 481

SQL Grouping and table values

I am trying to sort out for the website for my game, The scores are stored in SQL. The game stores everyones event scores, so naturally there are multiples for each player. I am trying to gather the top for each player. At the moment I am using a grouping 

SELECT TOP(250) * 
  FROM
       ( SELECT CharName, CharType, MAX(Point) AS SCORE
           FROM SOD2RecBySandurr
       GROUP BY CharName, CharType) AS derivedtbl_1 
          WHERE CharName 
            NOT LIKE '\[GM\]%' ESCAPE '\' 
           AND SCORE NOT LIKE '0' 
       ORDER BY SCORE DESC

This works fine, But I want to add kill count to the information I am displaying on the website. I tried adding KillCount to the group by part but it then shows everyones scores as naturally the kill count differs on each attempt.

SELECT TOP(250) * 
  FROM
                (SELECT CharName, CharType, KillCount, MAX(Point) AS SCORE
                   FROM SOD2RecBySandurr
               GROUP BY CharName, CharType, KillCount) AS derivedtbl_1 
                  WHERE CharName 
                    NOT LIKE '\[GM\]%' ESCAPE '\' 
                    AND SCORE NOT LIKE '0' 
               ORDER BY SCORE DESC

How can I make it do the character grouping based on charname but display the chartype and killcount, if i have killcount in the select but not in the group by then it throws back an error.

This one has me baffled :/ I understand why its not grouping them based on name (given the killcount wont match any other entries) but I just cant get my head around why it requires it to be in the group by for the select function.

EDIT:

Present results are: 

Player A - SCORE 1 - Kills 1
Player A - SCORE 2 - Kills 2
Player A - SCORE 3 - Kills 3
Player B - SCORE 1 - Kills 1

Where I want to see 

Player A - Score 1 - Kills 1 (Be that the max score)
Player B - Score 1 - Kills 1

I want to sort by max score but include the kill count for that score

Upvotes: 1

Views: 276

Answers (2)

Craig B
Craig B

Reputation: 481

I re-evaluated this after having used @clifton_h's answer for quite some time, i worked out how to get the corresponding killcount to match the line that had the highest score for the player:

Clifton_h's answer that I was using was:

SELECT CharName, CharType, KillCount, SCORE
            FROM (SELECT CharName, CharType, MAX(KillCount) AS KillCount, MAX(Point) AS SCORE
            FROM db.dbo.table
            GROUP BY CharName, CharType HAVING (MAX(Point) > 0)) AS SRC
            WHERE CharName NOT LIKE '\[GM\]%' ESCAPE '\' ORDER BY SCORE DESC;

However, this gave me the max killcount which may not necessarily be tied to the highest score for that player. What I did was a select statement where specifying killcount column in the top level select:

SELECT TOP(250) CharName, CharType, (SELECT KillCount FROM db.dbo.table WHERE Point=Score), Score
            FROM (SELECT CharName, CharType, MAX(Point) AS Score
            FROM db.dbo.table
            GROUP BY CharName, CharType HAVING (MAX(Point) > 0)) AS SRC
            WHERE CharName NOT LIKE '\[GM\]%' ESCAPE '\' ORDER BY SCORE DESC;

this is basically just giving me the killcount that lines up with the corresponding result from the lower aggregate.

Also worked as a Inner Join:

SELECT a.CharName, a.CharType, a.KillCount, a.Point 
FROM db.dbo.table AS a 
JOIN (SELECT CharName, CharType, MAX(Point) AS Score FROM db.dbo.table GROUP BY CharType, CharName HAVING (MAX(Point) > 0)) AS b 
ON a.CharName = b.CharName AND a.CharType = b.CharType AND a.Point = b.Score ORDER BY Point DESC;

Upvotes: 0

clifton_h
clifton_h

Reputation: 1298

From the way I read this, you want the top 250 players by points while showing their top kills in a match?

First, consider using OFFSET/FETCH to limit both the resources used and allows the client to dictate the pagination of the query. There are some limitations but you can read MSDN's ORDER BY CLAUSE to decide for yourself.

  • KILLCOUNT and POINT will never be the same, consider using MAX on both columns

You want to return the top scorers, so also keep the max kills in the scope. Depending on your session scope (Map? Series? All-Time?), you may need to use SUM and/or a OVER PARTITION BY clause (OVER clause is not supported in OFFSET/FETCH directly)

Think about how you are grouping them and any data not in the group by is being smashed. Therefore, those columns needs an aggregated function to provide any use (most cases).

If you were just wanting the top 250 scorers and their top kills, one solution might be the following:

SELECT *
FROM (SELECT     CharName, CharType, MAX(KillCount) AS KillCount, MAX(Point) AS SCORE
              FROM          SOD2RecB
              WHERE CharName NOT LIKE '\[GM\]%' ESCAPE '\'
                --   AND Point NOT LIKE '0'
              GROUP BY CharName, CharType
              HAVING MAX(Point) > 0) as SRC
ORDER BY SCORE DESC
FETCH FIRST 250 ROWS ONLY

WHERE and HAVING take SARG arguments. SQL Server might run faster filtering groups rather than rows depending on factors. However, consider using the WHERE clause anyways.

UPDATED (see comment section) When I wrote this, I was extremely tired, so there might be errors. Hopefully the main point is understood so you can correctly identify your business needs:

However, if you want the KillCount column that is related to the MAX(Point) returned, then the above query will not guarantee this result as you noted.

For the group by removes the relationship of the columns not in the GROUP BY. There are a number of posts on SO on this subject that go into greater detail, but essentially you need to use a subquery so that SQL Server can filter the returned results properly.

For performance reasons, methods exists to simplify the algorithm, but note that a table scan will be used. Table scans are not necessarily bad but running a full, un-optimized query on each row would be bad, so consider the effects on the query.

You may find the OUTER APPLY to be faster on large data sets instead of a CTE, but verify the methods used utilize the smallest comparisons necessary (Boolean is faster than numeric which is definitely faster than an implicit conversion of string datatypes) and SARGable as you can.

For this example, I chose to use an INNER JOIN instead of an OUTER APPLY purely as an example of a method. The main idea was comparing one value (point) for a group (CharName + CharType) with the max value returned to the set. Definitely consider the cardinality of the comparisons.

SELECT SRC.CharName
     , SRC.CharType
     , SRC.KillCount
     , TGT.SCORE
FROM SOD2RecB AS SRC
INNER JOIN (SELECT  CharName, CharType, MAX(Point) AS SCORE
            FROM          SOD2RecB
            WHERE CharName NOT LIKE '\[GM\]%' ESCAPE '\'
              AND Point NOT LIKE '0'
            GROUP BY CharName, CharType) as TGT ON CharName = TGT.CharName
                                                 , CharType = TGT.CharType
                                                 , Point    = TGT.Score
-- WHERE SRC.Point = TGT.Score
ORDER BY TGT.SCORE DESC
FETCH FIRST 250 ROWS ONLY

Upvotes: 1

Related Questions