Choosing the highest scored results from a table variable in SQL

Question

I have the following table variable in SQL Server 2005:

DECLARE @results TABLE (id int IDENTITY(1,1),
                        customerId int, 
                        salesId int, 
                        score int,
                        lastServiceDate datetime,
                        PRIMARY KEY(id));

I need an efficient way to clean the table or access the table's results, so that it returns only 1 result per salesId. If there is more than 1 result per salesId, it should show the row with the highest score, or in the case of a tie, the most recent lastServiceDate from the Customer table.

Right now, my test data looks like this:

id  customerId  salesId  score  lastServiceDate
1   950         418      3      2009-08-09 00:00:00.000
2   951         418      3      2009-08-19 00:00:00.000
3   952         418      1      2009-08-22 00:00:00.000
4   953         419      2      2009-08-15 00:00:00.000

I want something that would return, in this instance, just two rows--id 2 (top score/lastServiceDate for salesId 418) and id 4 (only result for salesId 419). In the end, I need to take the data in this table, and insert it into a JobResult table, selecting customerId, and salesId from @results, with these restrictions.

(This finishes a question asked in http://stackoverflow.com/questions/1343647.)

Answer 1

Try this:

  WITH highscore AS (
       SELECT r.salesid,
              MAX(r.score) 'maxscore',
              MAX(t.lastservicedate) 'maxservicedate'
         FROM @results r
     GROUP BY r.salesid)
  SELECT t.customerid,
         t.salesid,
         t.lastservicedate
    FROM @results t
    JOIN highscore hs ON hs.salesid = t.salesid 
                     AND hs.maxscore = t.score
                     AND hs.maxservicedate = t.lastservice
GROUP BY t.customerid,
         t.salesid

Answer 2

This will return the data you want:

SELECT id, salesId, score, lastServiceDate
 from (select id, salesId, score, lastServiceDate
         ,row_number() over(partition by SalesId order by score desc, lastServiceDate desc) ranking
        from @results) xx
 where xx.ranking = 1

and this will delete undesired rows from the table leaving only what you want:

DELETE @results
 where id in (select id
               from (select id, salesId, score, lastServiceDate
                       ,row_number() over(partition by SalesId order by score desc, lastServiceDate desc) ranking
                      from @results) xx
               where xx.ranking <> 1)

select * from @results

I tested this with the data you posted, but be sure to try it out with larger/more complex data sets.