Group by with results

If I have this table:

UserID | Score1 | Score2
1      | 10     | 5
2 开发者_JS百科     | 50     | 15
3      | 30     | 25
1      | 50     | 10
2      | 0      | 100
3      | 1      | 5

I want to sort this table to score1+score2, but I also want to get the seperate scores back.

But it has to be grouped by userId, and only returning the highest score of the user (so MAX score1+score2).

But I cannot do a group by because then I will rule out users with same scores. This is a headbreaker for me...

From above table the result should be:

UserID | Score1 | Score2 | TotalScore
2      | 0      | 100    | 100
1      | 50     | 10     | 60
3      | 30     | 25     | 55

---

SELECT UserID, Score1, Score2, Score1 + Score2 AS "Total"
FROM myTable
ORDER BY Score1 + Score2 DESC

you don't need a GROUP BY because it's not an aggregate function, it's just a calculated column.

Edit

You need to do a join on your max total score and your user id after you've grouped. Here's an example with a common table expression:

WITH maxScores AS (
    SELECT UserID AS "id", MAX(Score1 + Score2) AS "total"
    FROM scores 
    GROUP BY UserID
)
SELECT s.UserID, s.Score1, s.Score2, s.Score1 + s.Score2 AS "Total"
FROM scores s
JOIN maxScores m ON s.UserID = m.id
    AND s.Score1 + s.Score2 = m.Total

---

select UserID, Score1, Score2
    from YourTable
    order by Score1 + Score2

---

Are you looking something like this?

declare @t table( id int, s1 int, s2 int)

insert into @t (id, s1, s2) values (0, 10, 5)
insert into @t (id, s1, s2) values (1, 50, 15)
insert into @t (id, s1, s2) values (2, 20, 25)
insert into @t (id, s1, s2) values (1, 30, 0)

select id, s1, s2, (s1+s2) as 'total' from @t order by (s1+s2) desc, id asc

1   50  15  65
2   20  25  45
1   30  0   30
0   10  5   15

---

So then, is this what you want?

declare @t table( id int, s1 int, s2 int)

insert into @t (id, s1, s2) values (0, 10, 5)
insert into @t (id, s1, s2) values (1, 50, 15)
insert into @t (id, s1, s2) values (2, 30, 25)
insert into @t (id, s1, s2) values (1, 40, 0)

select id, MAX(s1) max_s1, MAX(s2) max_s2, SUM(total) total from
(select id, s1, s2, (s1+s2) as 'total' from @t) a
group by id
order by total 

I'm afraid you really did not specify what you want to do with s1 and s2 after grouping the rows with same id ...

---

How about this?

SELECT Q1.UserID, Q1.Score1, Q1.Score2, Q1.Total
FROM (SELECT UserID, Score1, Score2, (Score1+Score2) AS Total
    FROM myTable) AS Q1,
(SELECT UserId, Max(Score1 + Score2) As Total FROM myTable GROUP BY UserId) As Q2
WHERE Q1.UserId=Q2.UserId AND Q1.Total=Q2.Total
ORDER BY 4 DESC, 1

---

Does this answer it? Presuming that you would want to include duplicates of the same score1 + score2 for the same id (if not change the DENSE_RANK to ROW_NUMBER)

create table #data (id int,score1 int, score2 int)
insert into #data Values (0,10,5)
insert into #data Values (1,50,15)
insert into #data Values (2,30,25)
insert into #data Values (1,40,0)

** edit for sorting as per question revision**
;with scores AS
(
SELECT
id
,score1
,score2
,DENSE_RANK() OVER (PARTITION BY id ORDER BY score1 + score2 DESC) AS score_rank
FROM #data
)
SELECT 
id
,score1
,score2
,score1 + score2 AS sum_
FROM scores where score_rank = 1
ORDER BY score1 + score2 DESC