Latest instance of sql row without self join

Question

Input Rows

userid        |  no      |  version_no
--------------|----------|--------------  
abc           | 100      | 1  
abc           | 2        | 1  
abc           | 101      | 2  
abc           | 3        | 2
def           | 9        | 1
def           | 1        | 2
def           | 6        | 3
def           | 8        | 4

I'd expect the output of the query to be:

abc      |  104       |  2
def      |  8         |  4

Can I do this using any any method other than self-joins ? I am using sql server. The output no for abc - 104 is the sum of 101 and 3 from the inputs. If I have multiple rows for the latest version, I only want to display the sum of no's.

Apologies for editing the post multiple times.

Answer 1

You need to apply a ranking function after aggregation:

SELECT *
FROM
 ( SELECT userid, SUM(no) AS no_sum, version_no,
      ROW_NUMBER()
      OVER (PARTITION BY userid 
            ORDER BY version_no DESC) AS rn
   FROM table_name
   GROUP BY userid, version_no
 ) AS dt
WHERE rn = 1

Answer 2

To get just the aggregated results for the highest version_no without a self join, you can use TOP and ORDER BY:

SELECT TOP 1
     userid,
     sum(no),
     version_no
FROM your_table
GROUP BY userid, version_no
ORDER BY version_no DESC

TOP 1 will return only the first record in the result set ordered by the ORDER BY clause of version_no in descending order.