Hi David ,

Pretty poorly. Your method also performs poorly in relation to a traditional sub-select.

This may or may not be platform agnostic, although an important point its not a concern for me personally. BTW your code does need mods to run in a SQLServer environment 😉

See the attached image for a screen shot from profiler

drop table #testtab

go

create table #testtab

(

userid integer,

tableid integer

)

go

create unique clustered index idx1 on #testtab(userid,tableid)

go

with cter

as

(

select top(1000) row_number() over (order by (select null)) as r

from syscolumns a cross join syscolumns b cross join syscolumns c

)

insert into #testtab

Select r1.r,r2.r

from cter r1 cross join cter r2

select @@rowcount -- 1,000,000 rows

go

-- David Soussan Method

SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, case when t1.tableid >= t2.tableid then 1 else 0 end AS flg

FROM #testtab t1 INNER JOIN #testtab t2 ON t1.userid = t2.userid

GROUP BY t1.userid, t1.tableid, case when t1.tableid >= t2.tableid then 1 else 0 end

HAVING case when t1.tableid >= t2.tableid then 1 else 0 end = 1

go

-- count(*) sub select

select *,(Select count(*)

from #testtab t2

where t1.userid = t2.userid

and t2.TableId <= t1.tableId)

from #testtab t1

go

-- 2005 + , row_number()

Select * ,row_number() over (partition by userid order by tableid)

from #testtab