Posted by Alexander Kuznetsov on 10/02/27 11:45

> Here is a query based on Tom's table, but it returns 6 and not 7, because the first row is not counted

If there are gaps in PK, it gets slightly more exciting:

select count(*) from #t t1 join #t t2
on t1.PK < t2.PK
and t1. Amount < t2.amount
where not exists(
select 1 from #t t3
where (t1.PK < t3.PK) and (t3.PK < t2.PK)
)

[Back to original message]