Is there a better way to make this connection?

Question

I have a sales table for my sales agents by quarter:

  Agent quarter sales
 ----------------------------
 Alex Andersen 2011Q1 358
 Alex Andersen 2011Q2 289
 Alex Andersen 2011Q3 27
 Alex Andersen 2011Q4 2965
 Brian Blogg 2010Q3 277
 Brian Blogg 2010Q4 123
 Brian Blogg 2011Q1 783
 Brian Blogg 2011Q2 0
 Christy Cliff 2011Q2 777
 Christy Cliff 2011Q3 273
 Christy Cliff 2011Q4 111
 Christy Cliff 2012Q1 901

What is the simplest and most effective request for each agent at the beginning of a quarter and sales for this quarter?

It is easy to find out: “What is each agent first quarter?”:

SELECT agent, min(quarter) FROM salestable GROUP BY agent 

But this does not include sales figures, so I thought I would make an affiliate:

 SELECT agent, sales FROM salestable s1 JOIN ( SELECT agent AS e, MIN(quarter) AS q FROM salestable GROUP by employee ) AS q1 ON q1.e=s1.agent AND q1.mq=s1.quarter 

But this is unacceptably slow in my dataset. If I could use the cursor, it would only take one pass through the table, but using the query, it seems to require a connection. Is it correct?

Answer 1

Try this option and see if it is better:

 WITH cteRowNum AS ( SELECT agent, quarter, sales, ROW_NUMBER() OVER (PARTITION BY agent ORDER BY quarter) AS RowNum FROM salestable ) SELECT agent, quarter, sales FROM cteRowNum WHERE RowNum = 1;