CTE instead of cursor

Can anyone point me to an example of a CTE being used to step through a recordset instead of a cursor?

All of the example I've been able to find show recursion. I don't need that. I just want to step through a recordset one a开发者_JAVA百科t a time.

Thanks.

(EDIT)

Perhaps this is best asked another way. I assumed a CTE or cursor might be the best way to get the data I want but here's an example.

If I have a table with userids, codes and dates like the following, where the userid not a primary key.

2011-05-24 11:06:28.080     CODE1   199 
2011-05-24 11:06:28.080     CODE2   199 
2011-06-08 13:30:14.830     CODE2   209 
2011-06-08 13:30:14.313     CODE1   209 
2011-06-08 13:30:13.840     CODE2   209 
2011-06-08 13:30:13.450     CODE1   209 
2011-06-08 13:30:13.050     CODE2   209 
2010-05-07 10:45:06.800     CODE3   79  
2010-05-07 10:45:04.833     CODE3   79  
2010-10-15 07:30:16.193     CODE3   79  
2010-01-26 13:51:43.923     CODE3   79  
2010-01-26 13:51:43.923     CODE3   79  
2010-01-26 13:51:44.427     CODE3   79  
2010-01-26 13:51:45.103     CODE3   79  
2010-01-26 13:51:45.890     CODE3   79  
2010-01-26 13:51:46.733     CODE3   79  
2010-01-25 12:40:39.737     CODE3   81  
2010-01-25 12:40:40.890     CODE3   81  
2010-01-25 12:40:41.627     CODE3   81  
2010-01-25 12:40:43.277     CODE3   81  
2010-01-25 14:29:08.360     CODE3   81  
2010-01-21 19:36:34.660     CODE3   98  
2010-01-21 19:36:34.843     CODE3   98  
2010-01-21 19:36:35.013     CODE3   98  
2010-01-21 22:27:24.317     CODE3   83  
2010-01-21 22:31:21.443     CODE2   83  
2010-01-22 19:44:28.880     CODE3   83  

And I want to select from this table the oldest date and code for each user is it possible to do this using a CTE?

I can do a select where I group by customerid and get the MIN(datefield) but I get can't retrieve the code associated with that earliest date unless I add it to my group by clause... and then of course I get multiple rows for a customer, one with each associated code.

I found a solution that works where I create a temp table and fill it with the MIN(Date) and customer ID by grouping on the customerid. Then I update this temp table by joining it to the original table by customer and the date field to get the code associated with that row... but this seems to be a hack.

I was wondering if looping through the original table (sorted by customerid and date) and each time I see a new customer id inserting that record into a temp table, would be a cleaner solution... especially since the solution I'm finding assumes a unique datetime/customerid combination.

Here's an example of what I'm using now. It just seems like a hack and I'm wondering if a cusor (which I normally avoid) or a CTE would be a cleaner solution.

  DECLARE @Table1 TABLE 
(
    ClaimDate datetime, 
    VenueCode nvarchar(50), 
    CustomerID int
)
 INSERT INTO @Table1
  SELECT sc.CreateDate, v.code, sc.CSFCustomerID 
  FROM foo join foo1 on (snip)


DECLARE @Table2 TABLE
  ( 
  ClaimDate datetime,
  VenueCode nvarchar(50),
  CustomerID int)



 INSERT INTO @Table2
  select MIN(ClaimDate), NULL, CustomerID from @Table1 group by CustomerID

  UPDATE ft
  SET ft.SomeCode = t.VenueCode
FROM @Table2 ft
INNER JOIN @Table1 t ON ft.CustomerID = t.CustomerID
AND ft.ClaimDate = t.ClaimDate

Thanks, and I promise I'll be better about selecting answers in the future.

with qry
(
SELECT Min(Date), userID
FROM TABLE
GROUP BY UserID
)

SELECT Table.*
FROM TABLE 
INNER JOIN qry on qry.UserID = Table.UserID AND qry.Date = Table.Date

Though it is quite old post and the question title is not appropriate to illustrate your main task, that is finally: And I want to select from this table the oldest date and code for each user is it possible to do this using a CTE?

The answer is yes. You may try this:

-- CTE version of the SQL
WITH CTE (OldestClaimData, CustomerID)
AS
(
  SELECT
    MIN(ClaimDate), CustomerID
  FROM
    UserClaims
  GROUP BY
    CustomerID
)
SELECT DISTINCT
  UC.*
FROM
  UserClaims UC
INNER JOIN
  CTE ON (
    CTE.OldestClaimData=UC.ClaimDate
    AND
    CTE.CustomerID=UC.CustomerID
    AND 
    UC.VenueCode=(
      SELECT TOP 1
        VenueCode
      FROM
        UserClaims sub
      WHERE
        sub.ClaimDate=UC.ClaimDate
        AND
        sub.CustomerID=UC.CustomerID
    )
)
ORDER BY
    UC.CustomerID;

I've put my code and yours into an SQL Fiddle for your convenience.

P.D.: work on your accept rate.