GROUP BY highest score 1

[jasonsalas]

Hi everyone,

I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.

The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.

Here's my base query:

SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC

...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:

SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID

How can I write combine the two in a nested subquery to display only the top score per user?

 

[Pattycake245]

Could you give some data from each table, that might make it easier to write a query for it. Is top score the same thing as total votes?

Tim

 

[jasonsalas]

HI Pattycake245,

Here are the table schema:

USERS
- UserID (INT)
- Name (VARCHAR)

BALLOTS
- UserID (INT)
- SubmissionGUID (VARCHAR)
- TotalTally (INT) DEFAULT '0' -- this is incremented by varying values as a user makes correct selections
- WinningTeam (VARCHAR)

 

[jasonsalas]

also, you can just assume that there are multiple submission, with each submission consisting of 63 records in the BALLOTS table, each with the same SubmissionGUID.

so, a user's total point would be the SUM'med value of all of their records grouped by a SubmissionGUID. Thus, a user "JOHN", could have the following:

USER SUBMISSIONGUID NAME TOTALTALLY
---- ---- ----- ----------
1 kugiuvbiu JOHN 45
2 olhilugiu STEVE 32
3 oih98y897 MARK 31
1 89769gibi JOHN 29
1 0980jpo90 JOHN 13

I'd like just to select each unique USER's highest TOTALTALLY and display that, and forget about the others.

Clear as mud?

Thanks much!

 

[jasonsalas]

...so in the above case, i'd have only one record for JOHN, and one for everyone else.

 

[Pattycake245]

Sorry, it's late and I'm a little confused. So what kind of results does the first query display? It seems to be what you need. You say the second query gives you the highest vote total per user? Again, some actual data would be helpful, or even some data which shows the results of the two queries, as you say it may just be a simple sub-select you are missing.

Tim

 

[jasonsalas]

the first query displays ALL summed totals for EVERY user, and for as many ballots as they submitted. i just want to list the highest single total for each user, not all their posts.

 

[Pattycake245]

Oh, you beat me to the punch. OK I see now, how about this:

select UserID, max(TotalPoints) as HighestTotal
from(SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC) as results
group by UserID

Tim

 

[jasonsalas]

good call...i tried this already, but it keeps returning:

An ORDER BY clause in invalid in views, derived tables, and subqueries unless TOP is also specified.

 

[jasonsalas]

aah...here's the culprit (check the nested subquery):

select UserID, max(TotalPoints) as HighestTotal
from(SELECT top 100 percent a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC) as results
group by UserID

 

[Pattycake245]

Oh yes, no problem:

select UserID, max(TotalPoints) as HighestTotal
from(SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID) as results
group by UserID
order by UserID

Tim

 

[Pattycake245]

That way will work but my latest is cleaner, as you don't need to order in the sub-query anyway.

Tim

 

[jasonsalas]

thank you!

 

[Pattycake245]

you're welcome, sorry it took so many posts.
Tim

 

[jasonsalas]

yeah, and your second query doesn't require TOP.

 

[jasonsalas]

hi PattyCake,

I've got one last question...I've had to tweak my SPROC somewhat to incorporate a GUID value posted to the each record, and I'm trying to pass this in with the records. Recall that your query grouped the recordset in such was as to display ONLY the highest score per user.

I tried to add-in the GUID column, but it either (1) displays all users, or, using MAX(GUID) shows the wrong GUID for that user (right user, wrong result).

SELECT UserID,[Name],GUID,MAX(TotalPoints) AS [Totals]
FROM
(
SELECT a.UserID,UPPER(a.LastName) + ', ' + a.FirstName AS [Name],SUM(b.TotalTally) AS [TotalPoints],b.SubmissionGUID AS [GUID]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, UPPER(a.LastName) + ', ' + a.FirstName,b.SubmissionGUID
) AS [Results]
GROUP BY UserID,[Name],GUID
ORDER BY [Totals] DESC,[Name] ASC

Thanks again!

 

[Pattycake245]

So then you want to include the GUID for the record with the highest Total Points?

The query will need to be restructured a little to add this. Basically you'll need to do a separate derived table query to pull the submission guid's and then join on the first table with the max points to grab the appropriate guid. I may have a field or tablename misplaced as I am working with the data from earlier in your post, but it should be pretty close to this:

SELECT a.UserId, a.[Name], b.GUID, a.Totals
from
(SELECT UserID,[Name],MAX(TotalPoints) AS [Totals]
FROM
(
SELECT a.UserID,UPPER(a.LastName) + ', ' + a.FirstName AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, UPPER(a.LastName) + ', ' + a.FirstName
) AS [Results]
GROUP BY UserID,[Name]) a
JOIN
(SELECT UserID, SubmissionGUID GUID, TotalTally from Ballots) b
ON a.UserID=b.UserID
WHERE a.Totals=b.TotalTally
GROUP BY a.UserID, a.[Name], b.GUID
ORDER BY a.[Totals] DESC, a.[Name] ASC

Hope this does it for you.

Tim

 

[jasonsalas]

Thanks...that did the trick with the GUID field, but now is makes all the summed Totals columns come out with the max value...so all of a user's posts display that user's highest score, not all the varying scores.

 

[jasonsalas]

Tim, thanks a million again! I got it working with a variation, but it was getting the TOTALS columns to equal out that was the kicker:

SELECT a.UserID,a.[Name],a.[TotalPoints] AS [Totals],b.SubmissionGUID AS [GUID]
FROM
(
SELECT Results.UserID,Results.[Name],MAX(Results.TotalPoints) AS [TotalPoints]
FROM
(
SELECT a.UserID,UPPER(a.LastName) + ', ' + a.FirstName AS [Name],SUM(b.TotalTally) AS [TotalPoints],b.SubmissionGUID
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, UPPER(a.LastName) + ', ' + a.FirstName,b.SubmissionGUID
) AS [Results]
GROUP BY Results.UserID,Results.[Name]
) AS a

INNER JOIN
(
SELECT GUIDs.UserID,GUIDs.SubmissionGUID,MAX(GUIDs.Totals) AS [Totals]
FROM
(
SELECT a.UserID,a.SubmissionGUID,SUM(a.TotalTally) AS [Totals] FROM Ballots a
INNER JOIN Users b ON a.UserID = b.UserID
GROUP BY a.UserID,a.SubmissionGUID
) AS [GUIDs]
GROUP BY GUIDs.UserID,GUIDs.SubmissionGUID
) AS b
ON a.UserID = b.UserID
WHERE a.TotalPoints = b.Totals
ORDER BY [Totals] DESC, [Name] ASC