Sql Server - Solve This With Set-based Solution Instead Of Row Iteration

I'm trying to move some of my business logic out of my programs and into stored procedures. I'm really a VB.NET programmer, and not a SQL expert, but I'm learning more SQL and find

Solution 1:

EDIT Needs 1 more level of indirection for filtering by rank to work:

selectUser,Timefrom
(
  select*from
  (
      SelectUser,Time, rank() over (partitionby u.User orderby u.Time) as User_Rank
      from 
        your_table u
  ) UserRanks
) x
where User_Rank =1orderbyTime

Similar to araqnid and Royi's answers, but using WHERE NOT EXISTS rather than JOIN.

with CTE  as (
    selectuser, time, row_number() over (orderbytime) rn from MyTable
)  
select    CTE.user, CTE.time
from CTE CTE1
wherenotexists (selectuser, timefrom CTE CTE2 where CTE1.rn = CTE2.rn -1and CTE1.user = CTE2.user)

Solution 2:

This is one of those exceptions where a cursor is likely your best bet. Just try to limit the subset of data that you are going to iterate as much as you can.

Solution 3:

Finally :

;with CTE  as (
    selectuser, time, row_number() over (orderbytime) rn from MyTable
)  
select    CTE.user, CTE.time
from CTE leftjoin CTE other on other.rn = CTE .rn -1where other.user isnullor CTE .user<> other.user

Solution 4:

A row-based iteration is probably your best solution in SQL Server. Other database flavours allow you to example values from the previous/next row (lag and lead window functions), but SQL Server doesn't support those.

You could bodge something together like this:

with x as (
    selectuser, time, row_number() over (orderbytime) rn from source
)
select x.user, x.time
from x leftjoin x prev on prev.rn = x.rn -1where prev.user isnullor x.user <> prev.user

However, I suspect this is inconvenient and performs abominably.