Craig B Craig B - 3 months ago 7
SQL Question

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

Answer

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
Comments